Change Data Capture- Get only distinct latest changes

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:

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!

You have to group by all of the columns unless you are using an aggregate function

– jle
Sep 17 '18 at 13:22

1 Answer
1

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

Thank you! It worked. Can you please explain it as well. I am new to SQL queries.

– Skaranjit
Sep 17 '18 at 13:31

row_number() applies a running, incremented number to the group defined in the partition by clauses, starting with the first row defined in the order by. So, in this statement, a row number starting with 1 and going to n will be applied to every StudentUSI, starting with the latest LastModifiedDate since the order by is descending. At the end of this statement, the where clause returns only 1 row for each StudentUSI, being the latest one. Remove the where RN = 1 and you can see how the logic works @Skaranjit

– scsimon
Sep 17 '18 at 13:39

partition by

order by

where RN = 1

Thank you for the explanation, that really cleared the query. Awesome :D

– Skaranjit
Sep 17 '18 at 13:41

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

搜尋此網誌

Dfyjkt