仅选择最新的分组条目 [英] Select only newest grouped entries

问题描述

我有一张表，上面有这样的数据:

I have a table with data like this:

+-----------+-------+------+----------+
| timestamp | event | data | moreData |
+-----------+-------+------+----------+
| 100000000 |     1 |   10 |       20 |
| 100000001 |     1 |   15 |       10 |
| 100000002 |     1 |   30 |       30 |
| 100000003 |     1 |    5 |       50 |
| 100000004 |     2 |  110 |      120 |
| 100000005 |     2 |  115 |      110 |
| 100000006 |     2 |  130 |      130 |
| 100000007 |     2 |   15 |      150 |
+-----------+-------+------+----------+

现在，我只想为每个事件选择最新的行.所以最后我要设置这个结果:

Now I want to select only the newest rows for each event. So in the end I want to have this result set:

+-----------+-------+------+----------+
| timestamp | event | data | moreData |
+-----------+-------+------+----------+
| 100000003 |     1 |    5 |       50 |
| 100000007 |     2 |   15 |      150 |
+-----------+-------+------+----------+

到目前为止，我还无法做到这一点.在MySQL中，我可以使用"GROUP BY事件"，但是随后我从数据库中获得了一些随机行，而不是最新的行. ORDER BY没有帮助，因为分组是在订购前完成的.在按事件分组时使用MAX(timestamp)之类的聚合也无济于事，因为那时时间戳是最新的，但"data"和"moreData"仍来自其他随机行.

So far I was not able to do this. In MySQL I can use "GROUP BY event" but then I get some random row from the database, not the newest. ORDER BY doesn't help because the grouping is done before ordering. Using an aggregation like MAX(timestamp) while grouping by event also doesn't help because then the timestamp is the newest but "data" and "moreData" is still from some other random row.

我想我必须进行子选择，因此我必须首先获取最新的时间戳，如下所示:

I guess I have to do a sub select so I have to first get the latest timestamp like this:

SELECT MAX(timestamp), event FROM mytable GROUP BY event

，然后使用结果集过滤第二个SELECT，但是如何?也许有一个聪明的方法可以做到这一点而无需子选择?

and then use the result set to filter a second SELECT, but how? And maybe there is a clever way to do it without a sub select?

推荐答案

AFAIK，子选择是最好的选择，如下所示:

AFAIK, sub select is your best option, as follows:

SELECT * 
FROM mytable mt 
    JOIN ( SELECT MAX(timestamp) as max, event 
           FROM mytable 
           GROUP BY event) m_mt 
    ON (mt.timestamp = m_mt.max AND mt.event = m_mt.event);

这篇关于仅选择最新的分组条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！