Jürgen Hohlkopf Jürgen Hohlkopf - 1 year ago 104
SQL Question

Show same IDs together but ORDER BY timestamp

I have the following rows:

Person Age timestamp

Now I want that all rows to be sorted like this:
Order by timestamp, but Persons with the same Age should be grouped together!

enter image description here

Answer Source

You want to order by the largest time for each age. Most databases support the ANSI standard window functions, so you can do:

select t.*
from t
order by max(timestamp) over (partition by age) desc,
         timestamp desc;

In the "databases" that don't (MS Access, SQLite, MySQL):

select t.*
from t join
     (select age, max(timestamp) as maxts
      from t
      group by age
     ) tt
     on t.age = tt.age
order by maxts desc, age, timestamp desc;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download