替代 sql server 2008 中的 First_Value()

我有以下查询，它在 Oracle 和 DB2 中运行良好。但它在 SQL Server 2008 中不起作用，因为 First_Value 函数不可用于相同的 . 2008年有解决办法吗？

select NameGuid, Name, AncestorGuid, ProductGuid, PathLength
from (
    select 
    NameGuid, 
    Name, 
    AncestorGuid, 
    ProductGuid, 
    PathLength, 
    -- take every row from original query with the same Name as this,
    -- order those rows by PathLength (and NameGuid to disambiguate)
    -- and return the NameGuid of the first row in that "partition"
    first_value(NameGuid) over (partition by Name order by PathLength asc, NameGuid asc) MinNameGuid
    from ( 
        ... your original query ...
    )
)
where 
-- return rows whose NameGuid is the same as the NameGuid calculated by first_value(...)
NameGuid = MinNameGuid

注意：该查询是对我之前的查询的回答post https://stackoverflow.com/questions/20373082/query-to-return-unique-values-when-one-of-column-value-is-repeating/20374182#20374182

您可以在 SQL Server 2008 中尝试复制First_Value功能

;with CTE(NameGuid, Name, AncestorGuid, ProductGuid, PathLength) AS
(
  select 
  NameGuid, 
  Name, 
  AncestorGuid, 
  ProductGuid, 
  PathLength, 
-- take every row from original query with the same Name as this,
-- order those rows by PathLength (and NameGuid to disambiguate)
-- and return the NameGuid of the first row in that "partition"
-- first_value(NameGuid) over (partition by Name order by PathLength asc, NameGuid asc) MinNameGuid
 ROW_NUMBER() over (partition by Name order by PathLength asc, NameGuid asc) MinNameGuid
from ( 
    ... your original query ...
)a
)
Select c.NameGuid, c.Name, c.AncestorGuid, c.ProductGuid,
c.PathLength,c1.NameGUID
from CTE c
LEFT JOIN 
(SELECT NAMEGUID,Name from CTE where MinNameGuid = 1) C1
on 
c.Name = C1.Name
where c1.NAMEGUID is not NULL

SQL FIDDLE 演示 http://sqlfiddle.com/#!6/3186d/9