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
精彩评论