CODEWITHSUNDEEP
sqloracle-database
Buttlet
Buttlet

Reputation: 311

Select rows by comparing with next row

I have the following table,

value   caseid    version
-----   ------    -------
2.6     1         A
2.7     1         B
4.8     2         A
5.4     2         B
2.9     3         A
3.0     3         B

and I want to compare the value in rows with version A with those in version B with the same caseid, and only display rows that have a difference of greater than 0.1.

So, in the above example, the result should be:

value   caseid    version
-----   ------    -------
4.8     2         A
5.4     2         B

I've tried with a join, but then both rows get displayed in the same row. Is there a way to do this where values get displayed on different rows?

Thank you.

Upvotes: 1

Views: 115

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use lag() window analytic function :

with t1( value, caseid, version ) as
(
 select 2.6, 1, 'A' from dual union all
 select 2.7, 1, 'B' from dual union all
 select 4.8, 2, 'A' from dual union all
 select 5.4, 2, 'B' from dual union all
 select 2.9, 3, 'A' from dual union all
 select 3.0, 3, 'B' from dual 
), t2 as
(
select t1.value - lag(t1.value,1,t1.value) over 
       (partition by t1.caseid order by t1.version ) as diff,
       t1.* 
  from t1
)
select value, caseid, version
  from t2
 where t2.caseid in ( select caseid 
                        from t2 
                       where caseid = t2.caseid 
                         and diff > .1 )
 order by version;

VALUE   CASEID  VERSION
-----   ------  -------
4,80    2       A
5,40    2       B

Demo

Upvotes: 1

hotfix
hotfix

Reputation: 3396

you can do it by using union all


with tab as(
select 2.6 as value,     1  as caseid,       'A' as version from dual union all
select 2.7,     1,         'B' from dual union all
select 4.8,     2,         'A' from dual union all
select 5.4,     2,         'B' from dual union all
select 2.9,     3,         'A' from dual union all
select 3.0,     3,         'B' from dual 
)

select t1.value, t1.caseid, t1.version
  from tab t1
  join tab t2 on t1.caseid = t2.caseid
  where (t1.value- t2.value) > 0.1
union all
select t2.value, t2.caseid, t2.version
  from tab t1
  join tab t2 on t1.caseid = t2.caseid
  where (t1.value- t2.value) > 0.1

db<>fiddle here

Upvotes: 0

Related Questions