MySQL按最新时间戳选择 [英] MySQL Select By Newest Timestamp

问题描述

我在SO上看到过类似的问题，但是，我无法找到针对我的特定问题的解决方案. (仅供参考，这些不是我的真实专栏，只是一个简短的示例).

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

我有一个基本表:

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

我尝试过类似的操作:

 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.

非常感谢.

推荐答案

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

---

顺便说一句，如果您想知道为什么原来的查询不起作用，让我们分解一下:

By the way, in case you're curious why your original query didn't work, let's break down the pieces:

• 从my_table ...
中选择一些东西
• 其中user_2 = 22
• and timestamp = (有些值，暂时搁置一旁)
• 限制1

• select some stuff from my_table...
• where user_2 = 22
• and timestamp = (some value, let's put it aside for now)
• limit 1

现在，返回到该timestamp值，它来自您的子查询:

Now, coming back to that timestamp value, it comes from your subquery:

SELECT MAX( timestamp ) FROM my_table

请注意，此子查询不会基于user_2限制任何行，而是询问整个表中的最大时间戳 .该最大时间戳是上表中的第一个时间戳:(用户_1 = 23，用户_2 = 25，时间戳= 2012-08-10 22:00:00).

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:

• 从my_table ...
中选择一些东西
• 其中user_2 = 22
• 和时间戳= 2012-08-10 22:00:00
• 限制1

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

这篇关于MySQL按最新时间戳选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！