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.

Solution 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.

Solution 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 )

Solution 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.

Comments

[Dodinas]

Thanks very much. I was making this way too hard on myself. That's what I get for being awake the last 36 hours. :-)

[yshavit]

@Dodinas You're welcome! I find that coming from non-SQL programming, where we're taught over and over to break the problem into smaller problems, there's a tendency to do the same in SQL queries. Unfortunately, that often ends them up as more complex -- and less efficient, too. It's a mental switch that takes a bit to get the hang of. Btw, I edited my question to explain a bit as to where your original query got into trouble.

[Dodinas]

Terrific! That helps very much. Thanks again.

[Plummer]

Hahaha, this was genius. +1 for simplicity.

[Philibert Perusse]

I am wondering about the performance of that query when MANY (say 100K+) rows are there. The engine needs to sort everything before returning the first item of that list. If I create an index on the timestamp column, would the engine be intelligent enough to navigate the btree to find the one element I am looking for, instead of doing the whole sort?

[yshavit]

Yup, that's one of the main uses for an index. You can verify with EXPLAIN SELECT ... In this case, you'd want an index on (user_2, timestamp). Going left-to-right, an index can satisfy as many equalities as you want, and then ordering. Without the user_2 column in the index, thte engine would have to scan the timestamp index until it found a row with the right user_2.

[Miro Krsjak]

I came to the same solution, however, having a large table, even with indexes on timestamp, the query is very very slow. Any smarter way around?

[The Coder]

What advantage does this add to the accepted answer?