CODEWITHSUNDEEP
sqloracle-databasedate
Sam
Sam

Reputation: 422

Comparing the two consecutive rows in oracle from same table

Comparing the two consecutive rows in oracle with first time date change from same table. In below table it should return rows 5 & 6, 8 & 9

[1]: https://i.sstatic.net/6F6WN.png

Upvotes: 0

Views: 1123

Answers (1)

GMB
GMB

Reputation: 222472

You can use analytics functions: lag() (resp. lead()) lets you retrieve the value of date on the previous row (res. next row), which you can then compare to the value on the current row in an outer query:

select pk, name, date
from (
    select 
        t.*, 
        lag(date)  over(order by pk) lag_date,
        lead(date) over(order by pk) lead_date  
    from mytable t
) t
where lag_date <> date or lead_date <> date

Note: this assumes that column pk can be used to order the records (as shown in your sample data).

Upvotes: 1

Related Questions