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


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.

  • Would you provide code too. – Sunil Nepali Oct 31 '17 at 5:34

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
    MIN(col3) for col2 in ('Blue' as blue,'Orange' as orange)
  ) where blue<orange;


Tom   10      12
Lisa  14      15


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.