Mysql order by and group by query

Question

I have an SQL table and I made an SQL query which orders by latest update and groups by fleetID afterwards.

The table is in this image:

The query should return lines 3 and 5 but mine returns 1 and 3. My query is:

SELECT * FROM ( SELECT * from test order by lastupdate )l group by l.fleetid

Answer 1

You don't have a valid SQL statement -- even if your version of MySQL happens to accept it.

If you want the last update date for each fleet, then you want to filter the data, not aggregate it. That calls for where, not group by:

select t.*
from test t
where t.lastupdate = (select max(t2.lastupdate)
                      from test t2
                      where t2.fleetid = t.fleetid
                     );