MAX(Date) with a Sequence Number Data Touch Point

Question

I want to try and retrieve the Latest Row from a Table using MAX(Date). But I need to include an additional Sequence Number Data Column. The Sequence Number Data Column is making it unique and is messing up the MAX(Date) Select. So I'm getting as many rows as there are for a specific Patient ID.

How can I get the MAX(Date) from my Table and include the Sequence Number that it is associated with?

Answer 1

Use ranking function instead.
Here is one option with row_number()

select * from (select * ,  
row_number() Over(order by Date desc) from yourtable) t  
where rn=1  

Answer 2

Hi anonymous user

Please try this :

 create table #testdemo  
 (uniqueid int,  
  patientid int,  
  orderdate date)  
  insert into #testdemo  
  select 1,1000,'2021/1/2'  
  union all  
  select 2,1000,'2021/1/4'  
  union all  
  select 3,1001,'2021/1/2'  
  union all  
  select 4,1001,'2021/1/4'  
  
 with cte as( select * ,rn = ROW_NUMBER()over(partition by patientid order by orderdate desc) from #testdemo)  
 select * from cte where rn  =1  

result:

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.