How to join data table to select newest record in sql - Hack The Tech - Latest News related to Computer and Technology

logo

Get Daily Latest News related to Computer and Technology and hack the world.

Friday, June 4, 2021

demo-image

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



source https://stackoverflow.com/questions/67827078/how-to-join-data-table-to-select-newest-record-in-sql

No comments:

Post a Comment