How can I compare multiple fields/columns in Oracle with those fields/columns in the previous record

Question

I am trying to find duplicates in the Oracle table, but only those duplicates that are back to back, next to one another. For that I am using the following query

with a as
(
select  some_id, column1,  column2, column3, column4, column5, column6 
,lag(some_id) over (ORDER BY some_id) as prev_some_id
,lag(column1) over (ORDER BY some_id) as prev_column1
,lag(column2) over (ORDER BY some_id) as prev_column2
,lag(column3) over (ORDER BY some_id) as prev_column3
,lag(column4) over (ORDER BY some_id) as prev_column4
,lag(column5) over (ORDER BY some_id) as prev_column5
,lag(column6) over (ORDER BY some_id) as prev_column6

from   SOME_TABLE 
)
select * from a 
where some_id = prev_some_id
and column1 = prev_column1
and column2 = prev_column2
and column3 = prev_column3
and column4 = prev_column4
and column5 = prev_column5

However, for some reason, this query doesn't return duplicates. Can you please let me know what I am doing wrong?

Thank you in advance

Answer 1

Here is what worked for me

WITH  grid AS
    (  Select   ROW_NUMBER() OVER (Order By some_id, column1,  column2, column3, column4, column5, column6 ) "RN",
                 some_id, column1,  column2, column3, column4, column5, column6,
                  -9999 as prev_some_id, '<>' as prev_column1, '<>' as prev_column2, '<>' as prev_column3, -99 as prev_column4, -99 as prev_column5, -99 as prev_column6
        From       SOME_TABLE
        Order By some_id, column1,  column2, column3, column4, column5, column6 
    ),
    
sortedgrid as
(
    
Select  *
From    ( Select    RN, 
                 some_id, column1,  column2, column3, column4, column5, column6,
                    Nvl(LAG(some_id) Over(Order By RN), -999999) as prev_some_id,
                    Nvl(LAG(column1) Over(Order By RN), '**//**//')  as prev_column1,
                    Nvl(LAG(column2) Over(Order By RN), '**//**//') as prev_column2,
                    Nvl(LAG(column3) Over(Order By RN), '**//**//') as prev_column3,
                    Nvl(LAG(column4) Over(Order By RN), -999999)  as prev_column4,
                    Nvl(LAG(column5) Over(Order By RN), -999999)  as prev_column5,
                    Nvl(LAG(column6) Over(Order By RN), -999999) as prev_column6
          From      grid
          Order By  RN
        )

)

select * from sortedgrid

Where   some_id = prev_some_id And
        column1 = prev_column1 And
        column2 = prev_column2 And
        column3 = prev_column3 And
        column4 = prev_column4 And
        column5 = prev_column5 And
        column6 = prev_column6