CODEWITHSUNDEEP
mysqlselecttimestamp
Dodinas
Dodinas

Reputation: 6805

MySQL Select By Newest Timestamp

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.

Upvotes: 44

Views: 90263

Answers (5)

JaredCS
JaredCS

Reputation: 485

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.

Upvotes: 0

Kevin
Kevin

Reputation: 15964

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.

Upvotes: 6

RicardoMarquez
RicardoMarquez

Reputation: 99

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 )

Upvotes: 5

Bryan Bojorque
Bryan Bojorque

Reputation: 168

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']
}

Upvotes: 0

yshavit
yshavit

Reputation: 43391

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.

Upvotes: 89

Related Questions