pick latest latest file 's latest internal Id in same table desc sql

Question

pick latest latest file 's latest internal Id in same table desc sql
RefDate ID Name

---

20210503 1 PPP
20210504 2 PPP
20210505 3 PPP
20210502 1 PPP
20210503 4 xxx
20210504 5 xxx
20210505 6 xxx
20210502 2 xxx

but my result is to pick last record refdate: 20210502's Row of PPP & 20210502 -- 2--Row of xxx
Need Query,

Answer 1

Hi @Madhu P ,

Welcome to Microsoft Q&A!

Please refer below:

create table tableM  
(RefDate date,  
 ID int,  
  Name varchar(10))  
  
  insert into tableM values  
('20210503', 1 ,'PPP'),  
('20210504', 2 ,'PPP'),  
('20210505', 3 ,'PPP'),  
('20210502', 1 ,'PPP'),  
('20210503', 4 ,'xxx'),  
('20210504', 5 ,'xxx'),  
('20210505', 6 ,'xxx'),  
('20210502', 2 ,'xxx')  
  
;with cte as(  
select *,ROW_NUMBER() over (partition by name order by ID, RefDate) rn from tableM)  
select RefDate,ID,Name from cte where rn=1  

Output:

RefDate ID Name  
2021-05-02 1 PPP  
2021-05-02 2 xxx  

If above is not working, please provide more sample data or expected output with more details about your requirement.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.