CODEWITHSUNDEEP
sqlsql-server-2008
user1300011
user1300011

Reputation: 15

Display most recent date + unique ID

I have a SQL script (using SQL Server 2008):

SELECT distinct 
    ServiceIdentifier as ReceivedFrom, EventDate
FROM 
    [dbo].AttendanceEvent                                                  
WHERE 
    ServiceIdentifier in (5001, 5003, 5004)

that displays the date (column: EventDate) and organisation code (column: ServiceIdentifier) for each row in my table (name: AttendanceEvent).

I use the where serviceidentifier in (x,y,z) clause to enter in specific organisations I want to look at.

This script is pretty handy, except that it pulls every individual record which is too much and times out my SQL.

I want to refine the script to only pull the most recent date for each organisation.

Giving me 1 line per serviceidentifier.

The date format that EventDate is in is; 2014-08-23 19:31:44.163 if that impacts anything.

Any help and advice hugely appreciated!

E.H

P.S I looked through some of the other questions but couldn't really tell if mine was a duplicate, if you think it is, please let me know and I'll look there!

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Just use group by:

SELECT ServiceIdentifier as ReceivedFrom, max(EventDate) as EventDate
FROM [dbo].AttendanceEvent                                                  
where ServiceIdentifier in (5001, 5003, 5004)
group by ServiceIdentifier;

Upvotes: 2

Related Questions