0

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
1

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)
|improve this answer|||||

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.