This question already has answers here:
Is there a way to access the "previous row" value in a SELECT statement? (9 answers)
Closed 20 days ago.
I have a historized table in SQL Server where the id (part of a composite key) repeats when a record is altered. For now, I would like to check which rows with the same id have a difference ONLY in col_a. Suppose the following data sample:
row_id | id | col_a | col_b
------------------------------
1 | 1 |value_a | value_b
2 | 1 |value_a | value_c
3 | 1 |value_d | value_c
4 | 1 |value_e | value_c
5 | 1 |value_f | value_g
I would like my output to be the following:
row_id | id | col_a | col_b
-------------------------------
3 | 1 | value_d | value_c
4 | 1 | value_e | value_c
My data has a lot more columns, but the idea is that I really want to return a table that contains only rows that have been duplicated to track a change in col_a and nothing else. Preferably without having to create/write to a table. I added the row_id for illustration purposes, I would have to create it out of the composite key if required to solve the problem.
3条答案
按热度按时间a6b3iqyw1#
Here is how to achieve using LAG function:
e5nqia272#
With 2 columns, one that needs to change one that doesn't, then you could just do a
IS (NOT) DISTINCT FROM
(assuming SQL Server 2022 as no tags to suggest otherwise) to check thatCol_A
is different whileCol_b
is not with aLAG
/LEAD
n a CTEYou suggest that you have more columns though. As such an
INTERSECT
might work better here. I useROW_NUMBER
in a CTE and then anEXISTS
(with saidINTERSECT
) against the same CTE but on theROW_NUMBER
+1.:mbyulnm03#
data
use
DENSE_RANK
or use a reference of a CTE to another CTE
dbfiddle