mysql order and groupby

Question

Hi everyone! I got a MySQL table of logs. It has the following fields: id, status_id, object_id, created, modified.

I'm wondering what's the best way to get the latest status for each object?

Thanks in advance!

Edit: My last solution was to do

SELECT id, status_id, object_id, created, modified 
FROM (SELECT * FROM logs ORDER BY created DESC) AS a
GROUP BY object_id

It works but I think there's a better way of doing this. Anyone care to enlighten us here at SO? :)

Answer 1

try this, i've not checked this but should work

select object_id, status_id, MAX(created)
from ff
group by object_id having created = MAX(created)

the key is to use having function which will choose last item for every grouped object

EDIT:

I added status_id to select ;)

Answer 2

You're probably looking for

SELECT status_id FROM logs WHERE object_id = xx ORDER BY modified DESC LIMIT 0, 1

If you have another table with status codes, you can use

SELECT status_code FROM logs, status_codes_table
WHERE status_code_id = status_id AND object_id = xx
ORDER BY modified DESC LIMIT 0, 1

Edit:

If you want to have a table of all objects and their latest status codes, you can use:

SELECT object_id, status_id
FROM logs
GROUP BY object_id
HAVING modified = MAX(modified)