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.

yshavit · Accepted Answer · 2012-08-11T05:57:42.523

82

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.

edited Aug 11 '12 at 05:57

answered Aug 11 '12 at 05:38

yshavit

42,327
7
87
124

Thanks very much. I was making this way too hard on myself. That's what I get for being awake the last 36 hours. :-) – Dodinas Aug 11 '12 at 05:50
1

@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. – yshavit Aug 11 '12 at 06:00
Terrific! That helps very much. Thanks again. – Dodinas Aug 11 '12 at 06:07
Hahaha, this was genius. +1 for simplicity. – Plummer Jan 18 '13 at 02:18
1

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? – Philibert Perusse Oct 22 '13 at 01:58
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`. – yshavit Oct 22 '13 at 05:37

RicardoMarquez · Answer 2 · 2014-04-25T09:12:00.443

5

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 )

edited Apr 25 '14 at 09:12

answered Apr 25 '14 at 02:33

RicardoMarquez

99
1
3

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? – Miro Krsjak Sep 10 '19 at 14:06

score 4 · Answer 3 · edited May 23 '17 at 12:17

4

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.

edited May 23 '17 at 12:17

Community

1
1

answered Mar 11 '15 at 22:19

Kevin

14,655
24
74
124

What advantage does this add to the accepted answer? – The Coder Nov 21 '20 at 17:19

Bryan Bojorque · Answer 4 · 2012-08-11T06:08:52.953

0

This is all i got.

SELECT timestamp 
       FROM my_table 
       WHERE user_22 = '22' 
       ORDER BY timestamp DESC /*or ASC*/

And when you query it the codewould be

while($row = mysql_fetch_array(the sql query)){
$timestamp = $row['timestamp']
}

edited Aug 11 '12 at 06:08

answered Aug 11 '12 at 05:45

Bryan Bojorque

168
1
11

score 0 · Answer 5 · answered Jul 20 '23 at 17:35

Here is the solution I found to this problem:

 SELECT *
 FROM my_table AS t1
 WHERE user_2 = 22
 AND timestamp = (
 SELECT MAX( timestamp )
 FROM my_table AS t2
 WHERE t1.user_2 = t2.user_2 );

Simply Remove WHERE user_2 = 22 if you want to see the latest timestamp for each unique user.

MySQL Select By Newest Timestamp

5 Answers5

Linked

Related