SQL How to display all records in group by

Question

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:

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

Answer 1

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.

Answer 2

This will provide the results, ranked according to MAX(DateTime), and sorting by UserID in ascending order, in case of ties. The output shows the rows for each user in Descending order of DateTime.

SELECT
    MaxT.UserID,
    T.FirstName,
    T.LastName,
    T.DateTime
FROM
    (
    SELECT
        UserID,
        MAX(DateTime) AS MaxDateTime
    FROM
        MyTable
    GROUP BY
        UserID
    ) AS MaxT
    INNER JOIN MyTable AS T ON
        T.UserID = MaxT.UserID
ORDER BY
    MaxT.MaxDateTime DESC,
    MaxT.UserID ASC,
    T.DateTime DESC