Querying Last Entries group by DeviceId

Question

I have the database table logs as the following:

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

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

Answer 1

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