sql server - SQL How to display all records in group by -

i have table structure 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 way can write query data ordered datetime grouped userid? want record recent datetime @ top , remainder of users records follow. should next user recent date, followed of records. 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 don't know if possible single query. far i've come query:

select userid, firstname, lastname, max(datetime) maxdatetime table group userid, firstname, lastname order maxdatetime desc 

which gives me recent records in correct order, dont know how include remainder of records each user. i'm thinking need use join when do, group groups (as should).

edit: 1. yes, needs normalization. 2. it's sql server 2008 r2, confirmed on works 2008 , later that's perfect. 3. in case of tie, lower userid first works.

thanks!

you can use over() aggregate aggregate without grouping fields:

select userid      , firstname      , lastname      , datetime      , max(datetime) over(partition userid) maxdatetime table order maxdatetime desc,userid,datetime desc 

you can limit order by if don't want return maxdatetime:

select userid      , firstname      , lastname      , datetime          table order max(datetime) over(partition userid) desc, userid,datetime desc 

demo: sql fiddle

edit: note return lower userid first in case of tie.