How to join data table to select newest record in sql

I have two separate table LPG_usage and devices. In the LPG_usage table has 5 columns as (Device_id,Cylinder_Type,Date,Kg,Data_index ) and the devices table has 3 columns as (Device_id,User_name,Location)

I want to select newest record by considering the Date in the LPG_usage table for all of Device_id’s with the Location at the device table. The selected table include (Device_id,Cylinder_Type,Date,Kg,Location)

I made a query for that but it getting error as unknown column ‘td.Device_id’ in ‘on clause’ my code as following

select t.Device_id, t.Date, t.Kg,t.Cylinder_Type,td.Location
from lpg_usage t
inner join (
    select Device_id, max(Date) as Last_upDate
    from lpg_usage
    group by Device_id
) tm
INNER JOIN (
    SELECT Location FROM devices 
)td on t.Device_id = tm.Device_id and t.Date = tm.Last_upDate and t.Device_id = td.Device_id

I would be really grateful for any help,Thank you

Answer

Your error message is because your td subquery does not have device_id. You can add it by changing SELECT Location FROM devices to SELECT device_id, Location FROM devices. You will also need to move some of your conditions in the on for the join to td to a new on for the join to tm.

select t.Device_id, t.Date, t.Kg,t.Cylinder_Type,td.Location
from lpg_usage t
inner join (
    select Device_id, max(Date) as Last_upDate
    from lpg_usage
    group by Device_id
) tm
 on t.Device_id = tm.Device_id and t.Date = tm.Last_upDate
INNER JOIN (
    SELECT device_id, Location FROM devices 
)td
 on t.Device_id = td.Device_id

The above should work, but it’s not clear to me why you have the subquery for td. I would suggest considering:

select t.Device_id, t.Date, t.Kg,t.Cylinder_Type,td.Location
from lpg_usage t
inner join (
    select Device_id, max(Date) as Last_upDate
    from lpg_usage
    group by Device_id
) tm
 on t.Device_id = tm.Device_id and t.Date = tm.Last_upDate
INNER JOIN devices td
 on t.Device_id = td.Device_id

Another alternative to consider on modern DBs is:

SELECT device_id, data, kg, cylinder_type, location
FROM
(
 select t.Device_id, t.Date, t.Kg, t.Cylinder_Type, td.Location, RANK() OVER(PARTITION BY t.device_id ORDER BY t.data DESC) rk
 from lpg_usage t INNER JOIN devices td
  on t.Device_id = td.Device_id
)
WHERE rk = 1

The inner query uses a window function (RANK) to number the results in a given order (t.date DESC) for each device_id. The outer query uses that to only get the maximum date for each device_id. This uses RANK so it will return multiple rows for ties (like your original query). You could use ROW_NUMBER if you prefer to only get one record when there is a tie by date.