CODEWITHSUNDEEP
sqloracle-databasecomparison
CP3O
CP3O

Reputation: 429

How does one compare a row with its preceding row in Oracle SQL?

I'm trying to find the difference between every row with its preceding row in a table.

When I say difference, I mean whether two values are the same or not.

I'm not sure if I need a for loop, and also if the table has 30 to 40 columns does that mean that I will have to write the check for each of those 30-40 columns ?

This check needs to be done for all records with the same Non-Unique ID. ie. ID1 can exist for more than one record. Rows with ID1 needs to be compared with other rows of ID1.

Upvotes: 1

Views: 6817

Answers (2)

mas.morozov
mas.morozov

Reputation: 2736

You can use Oracle lag analitic function. Let us assume that your table is this:

CREATE TABLE tbl (no int, id int, val1 int, val2 int)

where no is the field on which data is ordered, id is your non-unique identifier (group identifier), val1 and val2 are values. Than you can use this query to find if each row is a duplicate of the previous row in its group:

select no, id, val1, val2,
 case when
  lag(val1,1) over (partition by id order by no) = val1 and
  lag(val2,1) over (partition by id order by no) = val2
 then 1 else 0 end duplicate
from tbl
order by no

Here it is in SQLfiddle

Upvotes: 3

vhadalgi
vhadalgi

Reputation: 7189

try this !!

comparing a table with it's own PK's

 select t1.value - t2.value from table t1, table t2 
    where t1.primaryKey = t2.primaryKey - 1

Upvotes: 0

Related Questions