Hi Masters,
The example below makes an attempt to swap the A before B when they appear together whilst ignoring the nulls in colC as well as still following the original order from colA but comes unstuck with id = 3333 and 4444 where for a few examples B comes in before the A and where a null is placed in between as well. I've spent a few days trying to incorporate the lag and lead functions as well as attempted a function passing in variables up to lag/lead position 20 for the real production dataset into a large case statement but still have some cases where B prefixes A.
I've tried searching around this and a few other sources but struggling to find a working solution so thought I'd ask.
The shortcomings in the results below are:
3333,3,1,A should appear before 3333,2,2,B
3333,7,1,NULL is incorrectly being placed in between 3333,6,1,A and 3333,5,2,B. it should come after 3333,5,2,B
4444,2,1,A should appear before 4444,1,2,B and then the rest should follow A first, B second.
with t1 as (select 1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 1111 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 2222 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 2222 as id, 3 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 1 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 2 as cola, 1 as colb, '' as colc from dual
union
select 3333 as id, 2 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 3 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 3 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 4 as cola, 1 as colb, '' as colc from dual
union
select 3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 6 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 7 as cola, 1 as colb, '' as colc from dual
union
select 4444 as id, 1 as cola, 1 as colb, '' as colc from dual
union
select 4444 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 4444 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 4444 as id, 2 as cola, 2 as colb, 'B' as colc from dual
union
select 4444 as id, 3 as cola, 1 as colb, 'A' as colc from dual)
select id, cola, colb, colc,
dense_rank () over (partition by id order by mn, rn) as rn2
from (select id, cola, colb, colc,
min (colb) over (partition by id, cola) as mn,
row_number () over (partition by id order by cola) rn
from t1)
order by id, rn2
So, when you say:
swap the A before B when they appear togetherHow exactly are you defining "appear together"?
If you mean "the rows are next to each other in the output of unordered select" then your requirement is impossible.
Oracle Database imposes no order on rows added to a heap table.
This "order" can change.
For example:
create table t enable row movement as
select rownum x, mod(rownum, 3) y, lpad('x', 1000, 'x') str
from dual
connect by level <= 10;
select x from t;
X
1
2
3
4
5
6
7
8
9
10
delete t where y = 0;
alter table t shrink space;
select x from t;
X
1
2
8
4
5
10
7
So you need a set columns to indicate rows are "next" to each other. Once you have this just add them to the order by as needed.
But if no such column group exists, then sorry, you're out of luck.