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
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
thank you