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

回答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.

回答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 )

回答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.

回答4:

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