Using ORDER BY and GROUP BY together

by

One way to do this that correctly uses group by: 

select l.* 
from table l
inner join (
  select 
    m_id, max(timestamp) as latest 
  from table 
  group by m_id
) r
  on l.timestamp = r.latest and l.m_id = r.m_id
order by timestamp desc

How this works:

  • selects the latest timestamp for each distinct m_id in the subquery
  • only selects rows from table that match a row from the subquery (this operation — where a join is performed, but no columns are selected from the second table, it’s just used as a filter — is known as a “semijoin” in case you were curious)
  • orders the rows

Leave a Comment