salvationishere
salvationishere

Reputation: 3511

How to write a T-SQL query to select top 1 records for each client?

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

Answers (4)

veljasije
veljasije

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

salvationishere
salvationishere

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

John Woo
John Woo

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

TimothyAWiseman
TimothyAWiseman

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

Related Questions