Specify the sort order for the GROUP BY query to get the oldest or newest record for each group
I need to get the most recent entry for each device from the update request log table. A device is unique based on the combination of its hardware ID and its MAC address. I'm trying to do this with GROUP BY
, but I'm not sure if it's safe as it looks like it might just return the "top record" (whatever SQLite or MySQL thinks).
I was hoping that this "top entry" could be hinted at ORDER BY
, but that doesn't seem to have any effect as both of these queries return the same entries for each device, in reverse 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 any other way to do this? I have seen several multiple related posts that involve sub-sampling. If possible, I would like to do it without subqueries, as I would like to know how to do it without it.
a source to share
You cannot "get the most recent record" using GROUP BY. GROUP BY joins many records together, so what you end up seeing / retrieving are not actual records from the table, but "virtual" records created from one or more table records.
If you really want the most recent entry for each device, you need to use a subquery. However, if you want to know the date of the most recent record for each device, you can use GROUP BY by placing a MAX aggregate around the field you created:
SELECT
extHwId,
mac,
MAX(created)
FROM upgradeRequest
GROUP BY extHwId, mac
ORDER BY created ASC
a source to share
This should do it ...
SELECT ur.extHwId,
ur.mac,
ur.created
from upgradeRequest ur
left outer join upgradeRequest ur2
on ur2.extHwId = ur.extHwId
and ur2.mac = ur.mac
and ur2.Created > ur.Created -- Join with all "later" entries
where ur2.Created is null -- Filter out all rows that have any later entries
... but this is inconvenient, probably won't work well on large tables (since you read and check almost every row), and will create duplicates if there are multiple records set with the same last date. Such a query could be much more efficient when executed with subqueries, for example the following form:
SELECT ur.extHwId,
ur.mac,
ur.created
from upgradeRequest ur
where not exists (select 1
from upgradeRequest ur2
where ur2.extHwId = ur.extHwId
and ur2.mac = ur.mac
and ur2.Created > ur.Created)
The advantage is that the database engine only has to find one row in a subquery (as opposed to reading all rows) to filter the row.
a source to share