Clever Geek Handbook

SQL Max () question

I am doing this now:

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

This gives me the latest post with the highest version #.

How do I get all the most recent messages? If I do this:

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

I am still getting messages with lower version numbers.

+2


source to share


2 answers


Here's a query that finds the most recent revision for each 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;
0


source 


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


+1


source






More articles:


All Articles