Specify sorting order for a GROUP BY query to retrieve oldest or newest record for each group

Question

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.

Answer 1

Try:

 SELECT extHwId, mac, MAX(created)
 FROM upgradeRequest
 GROUP BY extHwId, mac

Answer 2

You can't "get the most recent record" using GROUP BY. GROUP BY aggregates many records together so what you end up seeing/extracting are not the actual records from the table but "virtual" records constructed from one or more table records.

If you really want the most recent record for each device you'll need to use a subquery. However, if you only want to know the date of the most recent record for each device, you can use GROUP BY by putting a MAX aggregate around the created field:

SELECT
    extHwId, 
    mac, 
    MAX(created)
FROM upgradeRequest
GROUP BY extHwId, mac
ORDER BY created ASC

Answer 3

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 it is awkward, will probably not perform well on large tables (since you're reading and checking pretty much every row), and will produce duplicates if there are multiple entries set with the exact same most recent date. This kind of query can be much more efficient when done with subqueries, such as 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 here is the database engine only has to find 1 row in the subquery (as opposed to reading all rows first) to filter out a row.