Mysql join condition - restrict join to one row in group based on custom order
Sorry for the bad title, but it's hard to explain in one line.
I have a widget datasheet: each record has an id, created timestamp, device_id (foreign key) and status ("prelim" or "official"). I'm trying to get one, the newest, "most official" entry for each device - this means I want the newest official entry, but if it's not there, it will be the newest. Example
ID | created | device_id | status | data
1 | 100 | A | prelim | ##
2 | 105 | A | prelim | ##
3 | 107 | B | official | ##
4 | 109 | B | prelim | ##
I need to get line 2 because there is no official entry and this is the newest entry for device A and line 3 because this is the newest official entry for device B (any pre-entry after the official one should be ignored).
I am currently 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 want.
How did I restrict the join to only return the official string if it exists. I feel like I should be able to solve this with where in the inner query, or coalesce () in the join clause, but I can't completely wrap my brain around myself.
Thank.
source to share
A correlated subquery can be used 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')
stores the "official" records created desc
first , and stores the latest ones first. limit 1
selects one row that we can use in the where clause.
source to share
create table widget (
id smallint,
created smallint,
device_id char(1),
status varchar(20),
data varchar(20));
insert into widget (id, created, device_id, status, data) values (1,100,'A','prelim','abcd1234');
insert into widget (id, created, device_id, status, data) values (2,105,'A','prelim','efgh5678');
insert into widget (id, created, device_id, status, data) values (3,107,'B','official','ijkl9012');
insert into widget (id, created, device_id, status, data) values (4,109,'B','prelim','mnop3456');
select max(a.created),
a.device_id,
case
when b.id is null
then a.status
else b.status
end as status
from widget a
left join widget b
on a.device_id = b.device_id
and b.status = 'official'
where (
(a.status = 'prelim'
and b.status is null)
or
(b.status = 'official'
and a.status <> 'prelim')
)
group by a.device_id,
case
when b.id is null
then a.status
else b.status
end;
output:
+----------------+-----------+----------+
| max(a.created) | device_id | status |
+----------------+-----------+----------+
| 105 | A | prelim |
| 107 | B | official |
+----------------+-----------+----------+
2 rows in set (0.00 sec)
source to share