SQL How to display all records in group by

I have a table structure like this:

UserID  | Firstname | LastName | DateTime
-------------------------------------------
1       | John      | Doe      | 2015-04-27
2       | Karl      | Watts    | 2015-04-26
1       | John      | Doe      | 2015-04-25
3       | Jane      | Howard   | 2015-04-28

Is there a way I can write a query to get the data ordered by DateTime but grouped by UserID? I want the record with the most recent DateTime at the top and the remainder of that users records to follow. Then it should be the next user with the most recent date, followed by all of their records. Like this:

UserID  | Firstname | LastName | DateTime
-------------------------------------------
3       | Jane      | Howard   | 2015-04-28
1       | John      | Doe      | 2015-04-27
1       | John      | Doe      | 2015-04-25
2       | Karl      | Watts    | 2015-04-26

I just don’t know if this is possible with a single query. So far I’ve come up with this query:

SELECT UserID, FirstName, LastName, MAX(DateTime) as MaxDateTime
FROM Table
GROUP BY UserID, FirstName, LastName
ORDER BY MaxDateTime DESC

Which gives me the most recent records in the correct order, but I dont know how to include the remainder of the records for each user. I’m thinking I need to use a join but when I do, the group by groups everything together (as it should).

EDIT:

Yes, it needs normalization.
It’s SQL Server 2008 R2, I confirmed OVER works with 2008 and later so that’s perfect.
In case of tie, lower UserID first works.

Answer

You can use OVER() with an aggregate to get an aggregate without grouping all your fields:

SELECT UserID
     , FirstName
     , LastName
     , DateTime
     , MAX(DateTime) OVER(PARTITION BY UserID) as MaxDateTime
FROM Table
ORDER BY MaxDateTime DESC,UserID,DateTime DESC

You can limit it to the ORDER BY if you don’t want to return the MaxDateTime:

SELECT UserID
     , FirstName
     , LastName
     , DateTime         
FROM Table
ORDER BY MAX(DateTime) OVER(PARTITION BY UserID) DESC, UserID,DateTime DESC

Demo: SQL Fiddle

Edit: Note this will return the lower UserID first in case of a tie.

Advertisement

Answer