How to get distinct records?

Question

I have following records in DB table. I want to modify the query to bring only the distinct rep codes with Submitted_Date in descending order. how do I get that? This is what I tried

select distinct Rep_Code as repCode , Rep_Name as repName, Submitted_Date
FROM USBI_Interaction
where Rep_Code in 
  ('T203','A107') order by Submitted_Date desc 


T203    Amy N France    2/5/2019 6:52:15 AM
T203    Amy N France    2/5/2019 6:26:14 AM
T203    Amy N France    2/5/2019 6:23:27 AM
A107    Alan J Schmidt  2/5/2019 5:47:44 AM
T203    Amy N France    2/5/2019 1:24:35 AM

I should get result like following.(Each record with distinct rep code .If there are multiple records with same rep code get the record which has the latest submitted date)

T203    Amy N France    2/5/2019 6:52:15 AM
A107    Alan J Schmidt  2/5/2019 5:47:44 AM

Answer 1

Does aggregation do what you want?

select Rep_Code as repCode, Rep_Name as repName, MAX(Submitted_Date) as Submitted_Date
from USBI_Interaction
where Rep_Code in ('T203', 'A107') 
group by Rep_Code, Rep_Name
order by MAX(Submitted_Date) desc ;

Answer 2

You need row_number() :

select top (1) with ties Rep_Code as repCode , Rep_Name as repName, Submitted_Date
from USBI_Interaction
where Rep_Code in ('T203','A107') 
order by row_number() over (partition by Rep_Code order by Submitted_Date desc);    

Answer 3

use row_number() window function

    select repCode,repName,Submitted_Date
   from (
    select Rep_Code as repCode , Rep_Name as repName, Submitted_Date
    ,row_number() over(partition by Rep_Code ,Rep_Name 
                        order by Submitted_Date desc ) rn
           FROM USBI_Interaction
           where Rep_Code in ('T203','A107')
    ) t where t.rn=1 order by Submitted_Date desc