Selecting Records from a duplicate column whose value in another column is less than

Posted on

Question :

Below is an example of my table:

  Column A         Column B       Column C
    Tom              Blue           10
    Tom              Orange         12
    Alex             Blue           30
    Alex             Orange         29
    Lisa             Orange         15
    Lisa             Blue           14

Output:

Column A      Blue        Orange
 Tom           10           12
 Lisa          14           15

The output above returns when there are two duplicate values in Column A, Column B Blue value in Column C is less than Column B Orange value in Column C.

So far I have… select Column A, Column B as "Blue", Column B as "Orange" from table where Blue < Orange;. Obviously where Blue < Orange doesn’t work but that’s the logic.

I think I need to write a subquery but I cannot figure this out.

Answer :

Create Table

create table t1(
col1    varchar2(20), 
col2    varchar2(20), 
col3    number
);

Sample Data

insert into t1 
select 'Tom','Blue','10' from dual
union all
select 'Tom','Orange', '12' from dual
union all
select 'Alex','Blue','30' from dual
union all
select 'Alex','Orange', '29' from dual
union all
select'Lisa','Orange','15' from dual
union all    
select 'Lisa','Blue','14' from dual;

Select Query

select * from
  (
      select *
      from t1
  )
  pivot
  (
    MIN(col3) for col2 in ('Blue' as blue,'Orange' as orange)
  ) where blue<orange;

Output

COL1  BLUE    ORANGE
Tom   10      12
Lisa  14      15

dbfiddle

Leave a Reply

Your email address will not be published. Required fields are marked *