October 31, 2007 at 8:54 am
I need data from a table containing many columns including [Client ID], [SERVICE DATE], and [ROW_ID]
[ROW_ID] is the PK for the table. There are many entries for the same [CLIENT ID], each with a different [SERVICE DATE]. Many clients may have the same service date.
I need one row per client, based on the most recent service date.
If I try:
SELECT [Client ID], Max([SERVICE DATE]) AS [SERVICE DATE]
FROM dbo.CLSERVICES
GROUP BY [Client ID]
I will get the rows I need, but I can't figure out how to see the rest of the columns in the table.
I just can't get my head around this...
-noob
October 31, 2007 at 9:04 am
An expensive solution:
SELECT c2.*
FROM CLSERVICES c2
JOIN ( SELECT [Client ID]
, MAX([SERVICE DATE]) AS maxdate
FROM CLSERVICES c1
GROUP BY [Client ID]
) AS cc1 ON c2.[Client ID] = cc1.[Client ID]
AND c2.[SERVICE DATE] = cc1.maxdate
Regards,
Andras
October 31, 2007 at 9:08 am
On SQL Server 2005 you can also use CTEs which will improve the performance:
WITH maxvals ( [Client ID], maxdate )
AS ( SELECT [Client ID]
, MAX([SERVICE DATE])
FROM CLSERVICES c1
GROUP BY [Client ID]
)
SELECT c2.*
FROM CLSERVICES c2
JOIN maxvals AS cc1 ON c2.[Client ID] = cc1.[Client ID]
AND c2.[SERVICE DATE] = cc1.maxdate
Andras
October 31, 2007 at 9:31 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply