CODEWITHSUNDEEP
oracle-database
sleath
sleath

Reputation: 871

How do I compare rows in an Oracle Table?

I have a table that's like this 

rank continuationofrow
1    row 
2    row 
3    row 
4    row 
4    row  
4    row

I'm trying to identify the previous rows rank number within an Oracle statement. Any help is greatly appreciated. I've searched the internet and haven't found much.

Upvotes: 0

Views: 1667

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132570

You must have another column that establishes the order of the rows with the same rank, otherwise the concept of "previous row" is meaningless. Let's suppose you do:

seq rank continuationofrow
1   1    row 
2   2    row 
3   3    row 
4   4    row 
5   4    row  
6   4    row

No you can use an analytic function:

select seq, rank, continuationofrow, lag(rank) over (order by seq) as prev_rank
from mytable;


seq rank continuationofrow prev_rank
1   1    row  
2   2    row               1
3   3    row               2
4   4    row               3
5   4    row               4
6   4    row               4

Upvotes: 2

René Nyffenegger
René Nyffenegger

Reputation: 40489

select
  ...
 lag(rank, 1) over (order by ordering-columns)
from
  ..

Upvotes: 1

Related Questions