从多列中选择时如何在一列中使用DISTINCT

Question

我试图从两个不同的视图中选择列，但我只想在一个特定的列上使用DISTINCT语句。我原以为使用GROUP BY语句会起作用，但它抛出了一个错误。

SELECT DISTINCT
[Act].[ClientId]
,       [Ref].[Agency]
,       [Act].[FundCode]
,       [Act].[VService]
,       [Act].[Service]
,       [Act].[Attended]
,       [Act].[StartDate]
FROM [dbo].[FS_v_CrossReference_ALL] AS [Ref] 
INNER JOIN [dbo].[FS_v_Activities] AS [Act] ON [Ref].[VendorId] = [Act].[VendorId]
WHERE [Act].[StartDate] BETWEEN '1/1/2015' AND '12/31/2015'
GROUP BY [Act].[ClientId]

我想在[Act].[ClientId]上使用DISTINCT语句。有办法做到这一点吗？

Answer 1

假设您想要row_number():

SELECT ar.*
FROM (SELECT Act.*, Reg.Agency,
ROW_NUMBER() OVER (PARTITION BY Act.ClientId ORDER BY ACT.StartDate DESC) as seqnum
FROM [dbo].[FS_v_CrossReference_ALL] [Ref] JOIN
[dbo].[FS_v_Activities] Act
ON [Ref].[VendorId] = [Act].[VendorId]
WHERE [Act].[StartDate] >= '2015-01-01' AND
[Act].[StartDate] < '2016-01-01' 
) ar
WHERE seqnum = 1;

特别注意日期比较的变化：

1. 日期采用标准格式(YYYY-MM-DD或YYYYMMDD(
2. 用两个不等式代替CCD_ 3。如果日期确实是一个带有时间组件的日期/时间，这将使代码变得健壮