whiteatom whiteatom - 2 months ago 10
MySQL Question

mysql join condition - restrict join to single row in group based on custom order

Sorry for the poor title, but it's hard to explain in a single line.

I have a table of widget data - each record has an id, created timestamp, device_id (external key), and a status ('prelim' or 'official'). I'm trying to get a single, newest, "most official" record for each device - this means I want the newest official record, but if there isn't an official one, the newest prelim one. Example

ID | created | device_id | status | data
1 | 100 | A | prelim | ##
2 | 105 | A | prelim | ##
3 | 107 | B | official | ##
4 | 109 | B | prelim | ##


I should get row 2 because it's there is no official record, and it is the newest record for device A, and row 3 because it's the newest official record for device B (any prelim record after an official one should be ignored).

Currently, I'm using this query:

Select widget.* from widget
INNER JOIN (select id, device_id, status, max(created) as created from widget
group by device_id, status) max_widget
on widget.created = max_widget.created and widget.device_id = max_widget.device_id


This query gives me the newest rows grouped by device and status, so in the case of device B, it returns 2 rows - not what I need.

How did I restrict the join to return only the official row if it exists. I feel like I should be able to solve this with a where on the inner query, or a coalesce()in the join clause, but I can't quite wrap my brain around it.

Thanks.

Answer

You can use a correlated subquery for this:

select *
from widget w
where id = (
        select id
        from widget x
        where w.device_id = x.device_id
        order by field(status, 'official', 'prelim'), created desc
        limit 1
        );

order by field(status, 'official', 'prelim') keeps "official" records first and created desc keeps latest ones first in that. limit 1 fetches one row which we can use in the where clause.