SQLTeam.com | Weblogs | Forums

Sql query to select latest row

Hi Guys

I have the data below ( this is an example) and i am trying to create a query to pull the data to show the latest status of the city, i am struggling to do this

so from the data above (this is an example , new daily data is added every day) i need a query to show the latest row for each city only , so the below should be the rest

Can anyone help me with this?

Thank you

sample data
drop table sampledata
go 
create table sampledata
( 
[Date] date , 
Code varchar(20), 
City varchar(200), 
Area varchar(200), 
Status varchar(200)
)

set dateformat dmy 
go 

insert into sampledata select '01/02/2021','A1','Leeds','North','Not Complete'
insert into sampledata select '02/02/2021','A1','Leeds','North','Not Complete'
insert into sampledata select '03/02/2021','A1','Leeds','North','Not Complete'
insert into sampledata select '04/02/2021','A1','Leeds','North','Complete'
insert into sampledata select '25/01/2021','A2','Nottingham','Mid','Not Complete'
insert into sampledata select '26/01/2021','A2','Nottingham','Mid','Complete'
insert into sampledata select '15/02/2021','A3','London','South','Not Complete'
insert into sampledata select '16/02/2021','A3','London','South','Not Complete'

select * from sampledata
SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY [Date] DESC) AS rn
        FROM    sampledata
        ) a
WHERE   a.rn = 1

image

1 Like

thank you