How to get the latest record from each group using group clause in sql server

Question

I want to get top 1 record of each group order by device timestamp, so that I can get top 1 record of each device/imei.

SQL

select 
    o.DeviceTimeStamp, o.DeviceImei, o.OTI_A,OTI_T, 
    ts.latitude, ts.longitude 
from 
    Overview o
left join 
    TransformerLocations ts on o.DeviceImei = ts.imei
where 
    ts.latitude is not null
order by 
    o.DeviceTimeStamp desc

Sample data

2020-11-23 01:03:07.000 8673220311024   0   0   23.842163   91.280693
2020-11-23 01:01:06.000 8673220311024   0   0   23.842163   91.280693
2020-11-23 01:00:00.000 8645020301067   0   0   23.841940   91.280306

Expected output:

2020-11-23 01:03:07.000 8673220311024   0   0   23.842163   91.280693
2020-11-23 01:00:00.000 8645020301067   0   0   23.841940   91.280306

Answer 1

get top 1 record of each device/imei

One option uses window functions:

select *
from (
    select o.devicetimestamp, o.deviceimei, o.oti_a,oti_t, 
        ts.latitude, ts.longitude,
        row_number() over(partition by o.deviceimei order by o.devicetimestamp desc) rn
    from overview o
    inner join transformerlocations ts on o.deviceimei = ts.imei
    where ts.latitude is not null
) t
where rn = 1

Note that I changed the left join to an inner join: you have a condition in the where clause on the "right" table, so the join behaves as an inner join anyway.