Change Data Capture- Get only distinct latest changes

user_3315191 0
Viewed 580

I am trying to find all the distinct and latest changes only from a certain change data capture table. Here is the snap shot of the table. enter image description here

I tried to use this query:

select DISTINCT StudentUSI, sys.fn_cdc_map_lsn_to_time(__$start_lsn) TransactionTime, __$operation Operation, LastModifiedDate 
from cdc.Student_CT
where __$operation in (1,2,4)
ORDER BY StudentUSI;

But the returned result is:

enter image description here

Also if I try to use GROOUP BY, it says:

Column 'cdc.Student_CT.__$start_lsn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there any other way I could just get the most latest change with respect to the StudentUSI value? Thank you!

cdc sql-server
1 Answers

Use row_number()

select * from 
(select DISTINCT StudentUSI, sys.fn_cdc_map_lsn_to_time(__$start_lsn) TransactionTime, __$operation Operation, LastModifiedDate ,row_number() over(partition by StudentUSI order by LastModifiedDate desc) as rn
from cdc.Student_CT
where __$operation in (1,2,4))a where rn=1
user_69148640
Related