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

score 0 · Accepted Answer · answered Nov 22 '20 at 20:04

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.

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

1 Answers1