how to return the last timestamp date by doing group by of the same id in mysql

Question

I have a problem I have a table that has the serial column, equipment and a data timestamp has no primary key because this table would be a history, I in the table have several lines with the same serial but I need to bring the last date only I need to bring in one line, but when I put the group by serial order by timestamp desc it does not bring the last date inserted as I would it follows the query below:

SELECT * FROM spare_change.cad_checklist group by serial order by timestamp desc ;

The return would be

serial | equipamento |         data 
  60   |    item     | 2010-04-05 10:30:58

The last date would be for example the following 2010-04-06 09:59:10

Answer 1

You can use the MAX() to get the highest date and group by other:

Sqlfiddle - Online example:

SELECT 
  serial
  , equipamento
  , max(data) AS DataMaisRecente
FROM 
  cad_checklist 
GROUP BY 
  serial
  , equipamento
ORDER BY 
  DataMaisRecente DESC;

Answer 2

Probably this will work for you.

SELECT serial, max(data )
  FROM spare_change.cad_checklist 
 GROUP BY serial;

So you wouldn’t need order by, because you’d only return a single line