开发者

Querying Last Entries group by DeviceId

I have the database table logs as the following:

alt text http://www.freeimagehosting.net/uploads/16e974703a.jpg

I would like to extract the last entry of device, pollDate, status. For eg.

deviceId, pollDate, status

开发者_如何学编程1, 2010-95-06 10:53:28, 1

3, 2010-95-06 10:26:28, 1


I tried to run the following query but the distinct only selects the first records, not the latest

SELECT DISTINCT deviceId, pollDate, status
FROM logs
GROUP By deviceId
ORDER BY pollDate DESC

alt text http://www.freeimagehosting.net/uploads/5d181103f8.jpg

So, could you please help me to extract the latest entries from the table? Thanks.


If (deviceID, poll_date) is unique, you can do the following:

SELECT  *
FROM    (
        SELECT  deviceid, MAX(poll_date) AS md
        FROM    logs
        GROUP BY
                deviceid
        ) q
JOIN    logs l
ON      l.deviceid = q.deviceid
        AND l.poll_date = q.md
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜