Finding Max Date and Next Last Date

Transact-SQL
1

I have a data set like this:

PatientId       AssessDate          Reading
--------        ---------------    ----------------
1000            2021-01-28              31
1000            2020-12-29              28 
1000            2020-11-31              32
1000            NULL                    NULL
1001            2021-01-27              21
1001            2020-12-25              22
1001            2020-11-26              24
1001            NULL                    NULL
1001            2020-09-18              26
........

If I do a query like:

select PatientID, MAX(AssessDate)
From Table1
Group by PatientID

PatientId       AssessDate          
--------        ---------------    
1000            2021-01-28         
1001            2021-01-27

I get all the Max AssessDates for each patient.

However if I try adding the "readings" column as part of above I get all readings for each patient, not just the one associated with the max date. I kind of suspected this was going to happen, and thought there might be a way with partition/CTE table solution.

On top of this, I also want to get the 2nd last "reading" before the max assess date, bearing in mind some of the readings can be null, so it should pull the next most recent one (By date order).

I tried some variations of different ideas with "having", partion with rowID and can't seem to get it working.

Any idea how I can get the 2nd last "Max" date reading as well as show the "reading" without it affecting the data set returned? My end result would be this:

    PatientId       AssessDate          Reading
    --------        ---------------    ----------------
    1000            2021-01-28              31
    1000            2020-12-29              28 
    1001            2021-01-27              21
    1001            2020-12-25              22

Thanks

G

3
drop create sample data .. click arrow 
drop table #Sampledata 
create table #Sampledata 
(
PatientId   int ,    AssessDate  varchar(100) null,     Reading int null 
)
insert into #Sampledata values 
(1000,'2021-01-28',31   ),
(1000,'2020-12-29',28   ),
(1000,'2020-11-30',32   ),
(1000, NULL      ,NULL ),
(1001,'2021-01-27',21   ),
(1001,'2020-12-25',22   ),
(1001,'2020-11-26',24   ),
(1001,NULL      ,NULL ),
(1001,'2020-09-18',26   )

select * from #Sampledata

; with cte as 
(
	select 
	     PatientId
	   , Reading 
	   , cast(AssessDate as date ) as dt
       , ROW_NUMBER() over(partition by PatientId order by cast(AssessDate as date ) desc) as rn 
    from 
	   #Sampledata 
) 
select * from cte where rn in (1,2)
4

@harishgg1 That does what I want,

Thanks again

G