Devil's Advocate Devil's Advocate - 5 months ago 8
SQL Question

Get first record when all fields are identical except ID and Timestamp?

Here is my current query:

SELECT TOP 6 *
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType = 'Retrieve Document Set'


It returns data that looks similar to this:

ID TimeStamp tid Fruit Color User EventType
1 12:30:31 001 Apple Red Paul Retrieve Document Set
2 12:30:32 001 Apple Red Paul Retrieve Document Set
3 12:31:03 002 Orange Orange Steve Retrieve Document Set
4 12:31:04 002 Orange Orange Steve Retrieve Document Set
5 12:34:12 003 Banana Yellow Paul Retrieve Document Set
6 12:34:13 003 Banana Yellow Paul Retrieve Document Set


I would like my query to only return records 1, 3 and 5. Essentially, everything with a unique tid. How can I do this?

Answer

Try:

SELECT  distinct(*)
FROM    HS_IHE_ATNA_Repository.Aggregation a
WHERE   EventType = 'Retrieve Document Set'
AND     TimeStamp = (select min(b.TimeStamp) from from HS_IHE_ATNA_Repository.Aggregation b 
                      WHERE b.tid = a.tid)
ORDER BY ID asc