CODEWITHSUNDEEP
sqloracle-databasecompare
samistl
samistl

Reputation: 13

Comparing between rows in same table in Oracle

I am trying to find the best way to compare between rows in the same table. I wrote a self join query and was able to pull out the ones where the rates are different. Now I need to find out if the rates increased or decreased. If the rates increased, it's an issue. If it decreased, then there is no issue.

My data looks like this

ID       DATE           RATE
1010     02/02/2014      7.4
1010     03/02/2014      7.4
1010     04/02/2014      4.9
2010     02/02/2014      4.9
2010     03/02/2014      7.4
2010     04/02/2014      7.4

So in my table, I should be able to code ID 1010 as 0 (no issue) and 2010 as 1 (issue) because the rate went up from feb to apr.

Upvotes: 1

Views: 25624

Answers (4)

Shiva Kumar
Shiva Kumar

Reputation: 41

select a.&, case when a.rate > b.rate then 'issue' else 'no issue' end from table a join table b on a.ID=b.ID where a.date > b.date;

Upvotes: 0

Boneist
Boneist

Reputation: 23588

Sounds like a case for LAG():

with sample_data as (select 1010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 1010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 1010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
                     select 2010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
                     select 2010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 2010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual)
select id,
       dt,
       rate,
       case when rate > lag(rate, 1, rate) over (partition by id order by dt) then 1 else 0 end issue
from   sample_data;

        ID DT               RATE      ISSUE
---------- ---------- ---------- ----------
      1010 02/02/2014        7.4          0
      1010 03/02/2014        7.4          0
      1010 04/02/2014        4.9          0
      2010 02/02/2014        4.9          0
      2010 03/02/2014        7.4          1
      2010 04/02/2014        7.4          0

You may want to throw an outer query around that to only display rows that have issue = 1, or perhaps an aggregate query to retrieve id's that have at least one row that has issue = 1, depending on your actual requirements. Hopefully the above is enough for you to work out how to get what you're after.

Upvotes: 2

Jean-François Savard
Jean-François Savard

Reputation: 21004

You can achieve this with a select..case

select case when a.rate > b.rate then 'issue' else 'no issue' end
from yourTable a
join yourTable b using(id)
where a.date > b.date

See documentation for CASE expressions.

Upvotes: 3

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

select distinct ID from MyData latest
inner join MyData earlier on latest.id = earlier.id
where earlier.date < latest.date and earlier.rate < latest.rate

would be one way to get them unless you really need to select those with no issues?

Upvotes: 1

Related Questions