Using ORDER BY and GROUP BY together

greatest-n-per-group, group-by, mysql, sql

Solution

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

Problem

My table looks like this (and I'm using MySQL): ``` m_id | v_id | timestamp ------------------------ 6 | 1 | 1333635317 34 | 1 | 1333635323 34 | 1 | 1333635336 6 | 1 | 1333635343 6 | 1 | 1333635349 ``` My target is to take each m_id one time, and order by the highest timestamp. The result should be: ``` m_id | v_id | timestamp ------------------------ 6 | 1 | 1333635349 34 | 1 | 1333635336 ``` And i wrote this query: ``` SELECT * FROM table GROUP BY m_id ORDER BY timestamp DESC ``` But, the results are: ``` m_id | v_id | timestamp ------------------------ 34 | 1 | 1333635323 6 | 1 | 1333635317 ``` I think it causes because it first does GROUP_BY and then ORDER the results. Any ideas? Thank you.

Original source