Sort within row

oraclesorting

I wonder, if there is a way to sort data inside each row?
For example, I have table 

val1, 3, 2, 1
val2, 2, 3, 1
val3, 3, 1, 2
val4, 1, 3, 2
val5, 1, 2, 3

and want to select it sorted, as

val1, 1, 2, 3
val2, 1, 2, 3
val3, 1, 2, 3
val4, 1, 2, 3
val5, 1, 2, 3

Best Answer

Well, see below. This is something I would definitely never write down in a real environment (I would question the original problem rather).

drop table t1;
create table t1 (val varchar2(5), c1 number, c2 number, c3 number);
insert into t1 values ('val1', 10, 2, 5);
insert into t1 values ('val2', 7, 3, 1);
insert into t1 values ('val3', 4, 4, 8);
insert into t1 values ('val4', 1, 9, 2);
insert into t1 values ('val5', 3, 5, 3);
insert into t1 values ('val6', 8, 2, 10);
commit;
select * from t1;

VAL           C1         C2         C3
----- ---------- ---------- ----------
val1          10          2          5
val2           7          3          1
val3           4          4          8
val4           1          9          2
val5           3          5          3
val6           8          2         10

So far so good, just the sample data. Now do an UNPIVOT, then PARTITION BY val, ORDER BY the C values, then PIVOT back:

with t2 as (select * from t1 unpivot (c for cval in (c1 as 'A', c2 as 'B', c3 as 'C')) order by val, c),
     t3 as (select val, row_number() over (partition by val order by c) as rank, c from t2)
select * from t3 pivot (min(c) as c for rank in (1,2,3)) order by val;

VAL          1_C        2_C        3_C
----- ---------- ---------- ----------
val1           2          5         10
val2           1          3          7
val3           4          4          8
val4           1          2          9
val5           3          3          5
val6           2          8         10

Limitations:

  • need to adjust the SQL for extra columns
  • really hard to read and understand (in my opinion)
Related Question