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 | ##
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
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.