MySQL Select By Newest Timestamp

Question

I've seen some similar types of questions on SO, however, I have not been able to find a solution to my specific issue. (FYI, these are not my real columns, just a shortened example).

I have a basic table my_table:

user_1	user_2	timestamp	note(not part of table)
23	25	2012-08-10 22:00:00
24	22	2012-08-10 19:00:00	<=== I would like to return this row
24	22	2012-08-10 17:00:00
21	17	2012-08-10 15:00:00

So, what I want to do is be able to:

 1) Select the "newest" row, based on timestamp AND 
 2) Select the 'user_2' column when given a value.  

I have tried something like:

 SELECT *
 FROM my_table
 WHERE user_2 = 22
 AND timestamp = (
 SELECT MAX( timestamp )
 FROM my_table )
 LIMIT 1 

But this does not return the row I am looking for. Any help on fixing this query would be great.

Thanks very much.

Answer 1

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.

Answer 2

If someone has a similar problem in SQL Server, this will work for you (the suggested MySQL query in the previous post doesn't work in SQL Server):

SELECT * FROM my_table 
WHERE    timestamp =  ( SELECT MAX( timestamp ) FROM my_table 
                        WHERE user_2 = 22 )

Answer 3

Another method is to GROUP BY the user_2 column as you calculate MAX(timestamp). Doing so will make MAX(timestamp) calculate not the latest date in the entire table, but rather the latest timestamp for each group of records with the same user_2 value.

So, for example, your query could be:

SELECT * FROM my_table
WHERE user_2 = 22
AND timestamp =
  (SELECT MAX(timestamp) FROM my_table
   WHERE user_2 = 22
   GROUP BY user_2)
LIMIT 1;

This query is adapted from the answer I found in this excellent answer.