Specify sorting order for a GROUP BY query to retrieve oldest or newest record for each group
- by Beau Simensen
I need to get the most recent record for each device from an upgrade request log table. A device is unique based on a combination of its hardware ID and its MAC address. I have been attempting to do this with GROUP BY but I am not convinced this is safe since it looks like it may be simply returning the "top record" (whatever SQLite or MySQL thinks that is).
I had hoped that this "top record" could be hinted at by way of ORDER BY but that does not seem to be having any impact as both of the following queries returns the same records for each device, just in opposite order:
SELECT extHwId,
mac,
created
FROM upgradeRequest
GROUP BY extHwId, mac
ORDER BY created DESC
SELECT extHwId,
mac,
created
FROM upgradeRequest
GROUP BY extHwId, mac
ORDER BY created ASC
Is there another way to accomplish this? I've seen several somewhat related posts that have all involved sub selects. If possible, I would like to do this without subselects as I would like to learn how to do this without that.
