在SQL中选择多列时如何使用distinct

I have use simple inner join statement and getting result into CTE table. I want to select distinct 'ServiceId' from CTE. I have following query

我使用简单的内连接语句并将结果输入到CTE表中。我想从CTE中选择不同的'ServiceId'。我有以下查询

  SELECT DISTINCT(ServicesId), ServiceNo, ServiceDate, DealerCode FROM CTE_Temp

Suppose there are duplicate entries of ServiceId in CTE then I want to select first entry only and ignore rest of them.

假设在CTE中有重复的ServiceId条目,那么我想只选择第一个条目并忽略其余条目。

1 个解决方案

#1

You can use ROW_NUMBER() OVER() for this. Just replace the column in the ORDER BY to define what's first.

你可以使用ROW_NUMBER()OVER()。只需替换ORDER BY中的列即可定义第一列。

;WITH AnotherCTE AS(
    SELECT 
        ServicesId, ServiceNo, ServiceDate, DealerCode,
        RN = ROW_NUMBER() OVER(PARTITION BY ServicesID ORDER BY ServiceDate DESC)
    FROM CTE_Temp
)
SELECT 
    ServicesId, ServiceNo, ServiceDate, DealerCode
FROM AnotherCTE
WHERE RN = 1