question

castlepirate avatar image
castlepirate asked

SQL SELECT Distinct Query question

I have a database table called Events that has the following columns: [Event_ID][Date_Time][Computer][User] I am trying to find the most recent [Date_Time] for each distinct [Computer] and [User] SELECT DISTINCT [Date_Time],[Computer],[User] will give me every row as [Date_Time] makes them all unique. Doing a SELECT DISTINCT [Computer], [User] is perfect, except it doesn't have the most recent [Date_Time] with it. Thanks in advance, I am sure the answer is obvious to you all and I am just missing something simple.
sqldistinctdate-and-time-functions
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DenisT avatar image
DenisT answered
One of the ways to do it is to use MAX(): --not sure why my Format SQL button doesn't work! SELECT [Computer] , [User] , MAX([Date_Time]) AS Latest_Date_Time FROM dbo.Table GROUP BY [Computer] , [User] It will most def. help if you have an index on dbo.Table -- [Computer],[User],[Date_Time].
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image iainrobertson commented ·
Why the cte and join? Surely the aggregate is enough?
0 Likes 0 ·
DenisT avatar image DenisT commented ·
no reason :) you beat me before I edited the answer :)
0 Likes 0 ·
castlepirate avatar image castlepirate commented ·
Thank you very much it works perfectly!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.