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

Can someone please help me with this?

Answer 1

Correctly use the  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