Join a table to itself in reverse order in mysql

Question

I've got a table called tbl_device_log. It inserts a record on update trigger in another table.

model table

+------------+------------+---------------------+
| device_id  | status     | last_updated        |
+============+============+=====================+
| ab1        | S1         | 2020-10-05 10:00:00 |
+------------+------------+---------------------+
| ab2        | S1         | 2020-10-05 11:00:00 |
+------------+------------+---------------------+
| ab1        | S2         | 2020-10-05 12:00:00 |
+------------+------------+---------------------+
| ab2        | S2         | 2020-10-05 12:00:00 |
+------------+------------+---------------------+
| ab1        | S3         | 2020-10-05 14:00:00 |
+------------+------------+---------------------+

now, I want the result set contains statuses of the device at the start and end of the day.

Like

+------------+------------+----------------------+
| device_id  | status | last_status | date       |
+============+============+======================+
| ab1        | S1     | S3          | 2020-10-05 |
+------------+------------+----------------------+
| ab2        | S1     | S2          | 2020-10-05 |
+------------+------------+----------------------+

I tried to join the table in asc and desc order like this

select tbl_device_log.device_id, tbl_device_log.status, dl.status as last_status, date(tbl_device_log.last_updated) as date,tbl_
from tbl_device_log
join (select * from tbl_device_log group by device_id, date(last_updated) order by last_updated desc) as 
    dl ON (dl.device_id = tbl_device_log.device_id and date(dl.last_updated) = date(tbl_device_log.last_updated))
group by tbl_device_log.device_id, date(tbl_device_log.last_updated)
order by tbl_device_log.last_updated asc;

But I'm getting the following data

+------------+------------+----------------------+
| device_id  | status | last_status | date       |
+============+============+======================+
| ab1        | S1     | S1          | 2020-10-05 |
+------------+------------+----------------------+
| ab2        | S1     | S1          | 2020-10-05 |
+------------+------------+----------------------+

What am I doing wrong?

Answer 1

Please check next query as solution:

select distinct -- prevent duplicates
    device_id,
    -- get first status per device per day
    first_value(status) over (PARTITION BY device_id, date(last_updated) order by last_updated asc) as status,
    -- get last status per device per day
    first_value(status) over (PARTITION BY device_id, date(last_updated) order by last_updated desc) as last_status,
    date(last_updated) as date
from tbl_device_log;

Test DB fiddle on SQLize.online

Result:

device_id    status    last_status    date
ab1          S1        S3             2020-10-05
ab2          S1        S2             2020-10-05

Answer 2

I would suggest window functions:

select dl.*
from (select dl.*,
             row_number() over (partition by device_id, date(date) order by last_updated) as seqnum_asc,
             row_number() over (partition by device_id, date(date) order by last_updated desc) as seqnum_desc
      from tbl_device_log dl
     ) dl
where seqnum_asc = 1 or seqnum_desc = 1;