CODEWITHSUNDEEP
mysqljoin
whiteatom
whiteatom

Reputation: 1455

MySQL join most recent row in foreign table for each row

I have 2 tables, one of locations (assetid, timestamp and position), and one of status changes (assitid, timestamp and status). I'm trying to select all locations, joined to the "current status" which is determined by the most recent status change prior to the position timestamp (status.timestamp <= location.timestamp). 

Locations
ID  Asset  Timestamp  Lat/Lon
1   A      1000       Lat/Lon
2   A      2000       Lat/Lon
3   A      3000       Lat/Lon

Status
ID  Asset  Timestamp  Status
1   A      1000       active
2   A      2200       sleep

What I'm looking for:

LocID  Asset  Timestamp  Lat/Lon  Status
1      A      1000       Lat/Lon  active
2      A      2000       Lat/Lon  active
3      A      3000       Lat/Lon  sleep

I know the "join most recent row" question has been asked 1000 times, but my solution for that problem is not working here. In this case I'm not just looking for the most recent row, I need the most recent status relative to the timestamp in location table... and this is just beyond my abilities.

MariaDB 10.4.12

Thanks in advance for the help!

Edit: The suggestion to use row_number() below does actually solve the problem, but not efficiently. Running the query on my full data set, and it times out. Is there another approach to this problem?

Upvotes: 0

Views: 47

Answers (1)

The Impaler
The Impaler

Reputation: 48770

In MySQL 8.x you can use the ROW_NUMBER() window function to filter the related rows.

For example:

select loc_id, asset, timestamp
from (
  select
    l.id as loc_id,
    l.asset,
    l.timestamp,
    row_number() over(partition by l.asset order by s.timestamp desc) as rn
  from locations l
  join status s on s.asset = l.asset and s.timestamp <= l.timestamp
) x
where rn = 1

whiteatom edit: For the sake of future readers - this works. I just needed to change the partition by (which restarts the numbering; works like a group by). Once I changed it to "partition by l.asset, l.timestamp" it restarted the numbering at each new position's timestamp and the rows numbered 1 were all the correct ones.

Working example:

select loc_id, lts, asset, location, status
from (
  select
    l.id as loc_id,
    l.asset,
    l.timestamp as lts,
    l.location,
    s.status,
    row_number() over(partition by l.asset, l.timestamp order by s.timestamp desc) as rn
  from location l
  join status s on s.asset = l.asset and s.timestamp <= l.timestamp
) x
where rn = 1

Upvotes: 1

Related Questions