MySQL 按最新时间戳选择

MySQL Select By Newest Timestamp

提问by Dodinas

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

我在 SO 上看到了一些类似的问题，但是，我无法找到解决我的具体问题的方法。（仅供参考，这些不是我真正的专栏，只是一个简短的例子）。

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.

非常感谢。

回答by yshavit

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

• 从my_table...中选择一些东西
• 其中user_2= 22
• 和timestamp= （一些价值，让我们暂时搁置）
• 限制 1

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

现在，回到那个timestamp值，它来自您的子查询：

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

请注意，此子查询不限制基于的任何行user_2- 它询问整个表中的最大时间戳是多少。该最大时间戳是上表中的第一个：（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

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

... and you can see there isn't such a row.

...你可以看到没有这样的一行。

回答by RicardoMarquez

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

如果有人在 SQL Server 中遇到类似问题，这对你有用（上一篇文章中建议的 MySQL 查询在 SQL Server 中不起作用）：

SELECT * FROM my_table 
WHERE    timestamp =  ( SELECT MAX( timestamp ) FROM my_table 
                        WHERE user_2 = 22 )

回答by Kevin

Another method is to GROUP BYthe user_2column 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 groupof records with the same user_2value.

另一种方法是GROUP BY按user_2列计算MAX(timestamp)。这样做将不会MAX(timestamp)计算整个表中的最新日期，而是计算每组具有相同user_2值的记录的最新时间戳。

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.

这个查询改编自我在这个优秀答案中找到的答案。

回答by Bryan Bojorque

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