Help for getting Max record row

Question

Hello, How i can get the desired output. I want to get only row of every P1. Every P1 has many rows but I just want to get the max(StatusD) row where StatusD is not null. See below. Thanks

Create table #1 (P1 int, PS char(4), Sdate date, StatusD date)

Insert into #1 values (48, 'XX','2022-02-20','2022-02-11')

Insert into #1 values (48, 'XX','2022-03-26','2022-03-11')

Insert into #1 values (48, 'CC','2022-05-18','2022-04-10')

Insert into #1 values (48, 'RR','2022-06-18',NULL)

Insert into #1 values (48, 'RR','2022-07-18',NULL)

 

Insert into #1 values (49, 'XX','2021-01-18','2022-02-11')

Insert into #1 values (49, 'XX','2021-02-22','2022-03-11')

Insert into #1 values (49, 'DD','2021-04-12','2022-04-10')

Insert into #1 values (49, 'RR','2021-05-22',NULL)

Insert into #1 values (49, 'RR','2021-06-18',NULL)

 

Select distinct P1, Ps, Sdate, max(StatusD) Current_StatusD  from #1 where StatusD is not null

group by P1, Ps, Sdate order by 1

 

P1     Ps         Sdate           Current_StatusD

48     CC     2022-05-18      2022-04-10

49     DD     2021-04-12      2022-04-10

Answer 1

The row_number function comes in handy here:

; WITH CTE AS (
   SELECT P1, PS, SDate, StatusD,
          rownum = row_number() OVER(PARTITION BY P1 ORDER BY SDate DESC)
   FROM   #1
   WHERE  StatusD IS NOT NULL
)
SELECT P1, PS, SDate, StatusD
FROM   CTE
WHERE  rownum = 1

Answer 2

Check a method:

select x.P1, x.PS, x.Sdate, x.StatusD as Current_StatusD
from #1 x
left join #1 y on y.P1 = x.P1 and y.StatusD > x.StatusD
where x.StatusD is not null and y.P1 is null