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

Question

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 1

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