Compare rows with same identifier in same table-CodePudding

I am having a table in a database (SQL Server) which looks like this

 --------------------------------------------- 
| id | p_id         | dummyColumn2 | l_id     |
 --------------------------------------------- 
| 1  | 8            | someData1    | 20       |
 ---- -------------- -------------- ---------- 
| 2  | 13           | someData2    | 20       |
 ---- -------------- -------------- ---------- 
| 3  | 8            | someData1    | 21       |
 ---- -------------- -------------- ---------- 
| 4  | 13           | someData2_c  | 21       |
 ---------------------------------------------

Where I want to compare if rows with same p_id but with different l_id are same or not and if not same , get the p_id. (in this case query should return 13, as it's different from second record with p_id = 13 )

Some additional information: Imagine p_id represents the Unique Identifier and l_id represents which record is latest inserted , so I am comparing old rows (with l_id = 20) with new rows (with l_id = 21) and getting if it has changed or not.

Also please note this table is simplified , so in real case scenario it has much more columns and rows

Would appreciate any help and would like to know if this problem can be solved using Joins.

-- There is a Query I came up to but it doesn't seems to works

//Getting latest l_id 
declare @a int 
set @a = (Select top(1) l_id from thisTable order by l_id desc) 

SELECT NEW.p_id
FROM   thisTable AS OLD
       INNER JOIN thisTable AS NEW
         ON OLD.p_id = NEW.p_id
WHERE  OLD.l_id = @a-1
       AND NEW.l_id = @a
AND OLD.dummyColumn2 <> NEW.dummyColumn2 
AND ... (same for other columns)

EDIT: Imagine there are 1000 rows and 200 of them has l_id 18 , then another 200 has l_id 19 and so on . I am comparing rows with highest l_id to the one which was before that (like l_id = 21 and l_id = 20) higher l_id means that data is newer than data with lower l_ids

CodePudding user response：

Not sure if i got the question :)

Select * from
thistable as A
join thistable as B on A.P_ID=B.P_ID
where A.l_id<>B.l_ID and A.dummycolumn2<>B.dummycolumn2

CodePudding user response：

You can check this via exists:

select *
from t as t1
where exists (
    select *
    from t as t2
    where t2.p_id = t1.p_id -- t2 is same group
    and   t2.l_id < t1.l_id -- t2 is an older row
    and   t2.dummyColumn2 <> t1.dummyColumn2 -- status (?) is different
)