Help to get Rows with maxdate

Question

Hello - I have to get the rows from max(date1).The Keys are P1, t1, O1, Date1 to get the rows from max(date1).
create table #P (P1 char(10), t1 char(10), O1 char(2), Date1 datetime, OS char(2), E1 char(10), Z1 char(10), A1 char(10))

Insert into #P values ('F1','OOLL','9','2006-04-01', '11','A','B','1')
Insert into #P values ('F1','OOLL','9','2019-07-01', '11','A','B','1')

Insert into #P values ('F1','PPHH','01','2006-04-01', '11','A','B','1')
Insert into #P values ('F1','PPHH','01','2018-01-01', '11','C','A1','2')
Insert into #P values ('F1','PPHH','02','2006-04-01', '12','A','B','5')
Insert into #P values ('F1','PPHH','02','2020-01-01', '15','D','B','7')
Insert into #P values ('F1','PPHH','99','2006-04-01', '18','R','A','7')
Insert into #P values ('F1','PPHH','99','2020-01-01', '18','A','B','1')

Insert into #P values ('F1','RAAA','1','2006-04-01', '11','A','B','1')
Insert into #P values ('F1','RAAA','1','2013-05-01', '11','A','B','1')
Insert into #P values ('F1','RAAA','9','2006-04-01', '11','A','B','1')

Select * from #P order by p1, t1, O1, Date1

Expected Result:
P1 t1 O1 Date1 OS E1 Z1 A1
F1 OOLL 9 2019-07-01 00:00:00.000 11 A B 1
F1 PPHH 01 2018-01-01 00:00:00.000 11 C A1 2
F1 PPHH 02 2020-01-01 00:00:00.000 15 D B 7
F1 PPHH 99 2020-01-01 00:00:00.000 18 A B 1
F1 RAAA 1 2013-05-01 00:00:00.000 11 A B 1
F1 RAAA 9 2006-04-01 00:00:00.000 11 A B 1

Answer 1

Hi,

First of all, +1 for providing the DDL & DML :-)

Please check if this solve your needs

;With MyCTE as (  
 SELECT P1, t1, O1, Date1, OS, E1, Z1, A1, RN = ROW_NUMBER() OVER (PARTITION BY P1, t1, O1 ORDER BY Date1 DESC)  
 FROM p  
)  
SELECT P1, t1, O1, Date1, OS, E1, Z1, A1  
FROM MyCTE  
WHERE RN = 1