CODEWITHSUNDEEP
sqloracle-databasesql-order-by
AVS
AVS

Reputation: 109

How to rank columns of a same row in Oracle SQL

I have the following row in a table:

C1 |C2 |C3 |C4

 7 | 3 | 1 | 6

I would like to construct a query in such a way that an order is given to each column;

O1 |O2 |O3 |O4
 4 | 2 | 1 | 3

Is it possible to do this kind of row-by-row comparisons in a single query? Or is the only option to construct a complex case when statement?

Edit: the case when I am trying to bypass:

case 
    when C1 = greatest ( C1, C2, C3, C4) then 1
    when C1 >= C2 and C1 >= C3 and C1 < C4
      or C1 >= C2 and C1 <  C3 and C1 >= C4
      or C1 <  C2 and C1 >= C3 and C1 >= C4 then 2
    when C1 >= C2 and C1 <  C3 and C1 <  C4
      or C1 <  C2 and C1 >= C3 and C1 <  C4
      or C1 <  C2 and C1 <  C3 and C1 >= C4 then 3
    when C1 = least (C1, C2, C3, C4 ) then 4 
end as O1

In case of equal values, the index determines the order: if C2 = C3, O2 = 1, O2 = 3.

As you can image, this is quite prone to errors. Is there a way to do this comparison more elegant?

The comparison only needs to happen in a single row, the ordering of the columns in a single does not influence the order of the rows in the table.

Edit 2: there are multiple rows in the table, identified with ID_ROW.

Upvotes: 1

Views: 1424

Answers (3)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

I think you mean using unpivot and pivot consecutively :

create table mytable(C1 int, C2 int, C3 int, C4 int );
insert into mytable values(7, 3, 1, 6);

select * from
(
select CX variable, dense_rank() over ( order by value ) value
  from mytable
unpivot (value for CX in (C1, C2, C3, C4))
)
pivot
(
 max(value) as vl for(variable) in ('C1' AS C1, 'C2' AS C2, 'C3' AS C3, 'C4' AS C4 )
);

C1_VL   C2_VL   C3_VL   C4_VL
  4       2       1       3

SQL Fiddle Demo

Upvotes: 1

user5683823
user5683823

Reputation:

One way is to unpivot the data, use the ROW_NUMBER() analytic function and re-pivot. This approach may take longer than working directly on each row, but is much easier to maintain. You will need to decide which goal is more important.

EDIT - based on the desired handling of ties (which is still not clear), perhaps ROW_NUMBER() should be replaced with either RANK() or DENSE_RANK(); othewise the solution is the same. END EDIT

create table inputs ( id_row, c1, c2, c3, c4 ) as
  select 101, 7, 3, 1, 6 from dual union all
  select 102, 1, 5, 5, 2 from dual union all
  select 103, 0, 0, 0, 0 from dual union all
  select 104, 8, 3, 4, 1 from dual
;

select id_row, 
       c1_val as c1, c2_val as c2, c3_val as c3, c4_val as c4,
       c1_rn  as o1, c2_rn  as o2, c3_rn  as o3, c4_rn  as o4
from   (
         select  id_row, val, col, 
                 row_number() over 
                     (partition by id_row order by val, col) as rn
         from    inputs
         unpivot ( val for col in (c1 as 1, c2 as 2, c3 as 3, c4 as 4) )
       )
pivot  ( min(val) as val, min(rn) as rn 
         for col in (1 as c1, 2 as c2, 3 as c3, 4 as c4) )
;

  ID_ROW     C1     C2     C3     C4     O1     O2     O3     O4
-------- ------ ------ ------ ------ ------ ------ ------ ------
     101      7      3      1      6      4      2      1      3
     102      1      5      5      2      1      3      4      2
     103      0      0      0      0      1      2      3      4
     104      8      3      4      1      4      2      3      1

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Complex nested case-when is not necessary, you can use a "simple" case-when with a sum (although a bit tedious)

select t.*,

     case when c1>c2 then 1 else 0 end
     + case when c1>c3 then 1 else 0 end
     + case when c1>c4 then 1 else 0 end + 1 as q1,

     case when c2>c1 then 1 else 0 end
     + case when c2>c3 then 1 else 0 end
     + case when c2>c4 then 1 else 0 end + 1 as q2,

     case when c3>c1 then 1 else 0 end
     + case when c3>c2 then 1 else 0 end
     + case when c3>c4 then 1 else 0 end + 1 as q3 ,

     case when c4>c1 then 1 else 0 end
     + case when c4>c2 then 1 else 0 end
     + case when c4>c3 then 1 else 0 end + 1 as q4 

FROM table1 t;

| c1 | c2 | c3 | c4 | q1 | q2 | q3 | q4 |
|----|----|----|----|----|----|----|----|
|  7 |  3 |  1 |  6 |  4 |  2 |  1 |  3 |
|  6 |  5 |  4 |  1 |  4 |  3 |  2 |  1 |

Upvotes: 3

Related Questions