Selecting Records from a duplicate column whose value in another column is less than
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&lt;orange;


Output

COL1  BLUE    ORANGE
Tom   10      12
Lisa  14      15


dbfiddle