Reputation: 3511
I have a simple script that I am trying to get the most recent records per client on. How do I do this in TSQL? This is my code currently, however, this is only selecting one record total. This one record displays most recent record for ALL clients and not EACH client! How can I reformulate this please?
SELECT TOP 1
C.ClientID, actual_date
From ClientRecords C
WHERE (@ClientID is NULL or C.Client_ID = @ClientID)
Group by C.ClientID, actual_date
ORDER BY C.actual_date
Upvotes: 3
Views: 16462
Reputation: 7092
You can use SUBQUERY for that purpose:
SELECT
C.ClientID ,
(SELECT MAX(C1.actual_date) FROM ClientRecords C1 WHERE C1.Client_ID = C.Client_ID) AS MaxDate
FROM ClientRecords C
WHERE (@ClientID is NULL or C.Client_ID = @ClientID)
Group by C.ClientID, actual_date
ORDER BY C.actual_date
Upvotes: 0
Reputation: 3511
Thanks guys, but I found a little more satisfactory solutoin to this:
WITH rs AS
(
SELECT
C.ClientID, actual_date,ROW_NUMBER() OVER(ORDER BY C.ClientID, actual_date)rid
From ClientRecords C
)
SELECT * FROM rs WHERE rid =1
Upvotes: 0
Reputation: 263723
Aggregate the column by using MAX()
function on actual_date
SELECT C.ClientID, MAX(actual_date) max_DATE
From ClientRecords C
WHERE (@ClientID is NULL or C.Client_ID = @ClientID)
Group by C.ClientID
ORDER BY C.actual_date
Upvotes: 4
Reputation: 14873
This has not been tested, but it should look something like:
select
c.clientId, max(actual_date) as Actual_date
from clientrecords C
group by c.clientID
order by c.clientID
That will give you the highest actual date for each client, ordered by the clientId.
Upvotes: 1