如何为每个键值选择带有最近时间戳记的行? [英] How can I select rows with most recent timestamp for each key value?

问题描述

我有一张传感器数据表.每行都有一个传感器ID，一个时间戳和其他字段.我想为每个传感器选择一个带有最新时间戳的单行，包括其他一些字段.

I have a table of sensor data. Each row has a sensor id, a timestamp, and other fields. I want to select a single row with latest timestamp for each sensor, including some of the other fields.

我认为解决方案是按传感器ID分组，然后按max(timestamp)排序，如下所示:

I thought that the solution would be to group by sensor id and then order by max(timestamp) like so:

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable 
GROUP BY sensorID 
ORDER BY max(timestamp);

这给我一个错误，说"sensorField1必须出现在group by子句中或在聚合中使用."

This gives me an error saying that "sensorField1 must appear in the group by clause or be used in an aggregate."

解决此问题的正确方法是什么?

What is the correct way to approach this problem?

推荐答案

出于完整性考虑，以下是另一种可能的解决方案:

For the sake of completeness, here's another possible solution:

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable s1
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID)
ORDER BY sensorID, timestamp;

我认为很容易解释，但是

Pretty self-explaining I think, but here's more info if you wish, as well as other examples. It's from the MySQL manual, but above query works with every RDBMS (implementing the sql'92 standard).

这篇关于如何为每个键值选择带有最近时间戳记的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！