MySQL query to get list of latest updated pages, but only one result for every slug?

Question

I have a table for pages with fields slug (string, not unique) and updated (datetime). I want to retrieve a list of last updated pages based on the updated field, but I only want one result for every slug (since slugs can be re-used across pages in this case). What to do?

I tried this:

SELECT * FROM `pages`
GROUP BY `slug`
ORDER BY `updated` DESC

That gives only one result per slug, but not always the last updated one.

Answer 1

This should work for you:

SELECT *
FROM pages p1
LEFT JOIN pages p2 ON p1.slug = p2.slug AND p1.updated < p2.updated
WHERE p2.updated IS NULL
ORDER BY p1.updated DESC

See http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html.

Answer 2

This is MSSQL - but will return 'ties' if a page is updated 2 or more times at exactly the same datetime.

SELECT * FROM pages
WHERE Updated In
(
SELECT Max(Updated)
FROM pages
GROUP BY slug
)