Jürgen Hohlkopf Jürgen Hohlkopf - 6 months ago 21
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


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;