Using ORDER BY and GROUP BY together

Question

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.

Answer 1

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

Answer 2

If you really don't care about which timestamp you'll get and your v_id is always the same for a given m_i you can do the following:

select m_id, v_id, max(timestamp) from table group by m_id, v_id order by max(timestamp) desc 

Now, if the v_id changes for a given m_id then you should do the following

select t1.* from table t1 left join table t2 on t1.m_id = t2.m_id and t1.timestamp < t2.timestamp where t2.timestamp is null order by t1.timestamp desc