MySQL Select By Newest Timestamp

Question

SELECT * FROM my_table -- standard stuff
   WHERE user_2 = 22 -- predicate
   ORDER BY timestamp DESC -- this means highest number (most recent) first
   LIMIT 1; -- just want the first row

Edit:

By the way, in case you’re curious why your original query didn’t work, let’s break down the pieces:

select some stuff from my_table…
where user_2 = 22
and timestamp = (some value, let’s put it aside for now)
limit 1

Now, coming back to that timestamp value, it comes from your subquery:

SELECT MAX( timestamp ) FROM my_table

Note that this subquery doesn’t restrict any rows based on user_2 — it asks for what’s the max timestamp in the whole table. That max timestamp is the first one in your table above: (user_1 = 23, user_2 = 25, timestamp = 2012-08-10 22:00:00).

So, let’s plug that back to the top-level query:

select some stuff from my_table…
where user_2 = 22
and timestamp = 2012-08-10 22:00:00
limit 1

… and you can see there isn’t such a row.

Leave a Comment Cancel reply