SQL Max() question

Question

Right now I'm doing this:

SELECT * FROM messages WHERE location_id = 7 AND date(date) <= date('now', 'localtime') ORDER BY date,revision LIMIT 1

This gives me the most recent message, with the highest revision #.

How can retrieve all of the most recent messages? If I do:

SELECT * FROM messages WHERE date(date) <= date('now', 'localtime') ORDER BY date,revision

I still get messages with lower revision numbers.

Answer 1

Here's a query that finds the most recent revision per location:

SELECT m1.* FROM messages m1
LEFT OUTER JOIN messages m2 
  ON (m1.location_id = m2.location_id AND m1.revision < m2.revision)
WHERE m2.location_id IS NULL 
ORDER BY date, revision;

Answer 2

SELECT * FROM messages m1
WHERE date(date) <= date('now', 'localtime') 
  and revision = (select max(revision) from messages m2 where date(m2.date) = date(m1.date))
  and location_id = 7
ORDER BY date,revision