Skip to Main Content
  • Questions
  • Re-ordering selective rows in a alpha column

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Richard.

Asked: October 21, 2017 - 3:40 am UTC

Last updated: October 24, 2017 - 8:00 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

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


with LiveSQL Test Case:

and Chris said...

So, when you say:

swap the A before B when they appear together

How 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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Chris

Richard Shaw, October 23, 2017 - 12:17 pm UTC

Hi Chris,

Thanks for that answer. I managed to work out a solution as per below. I'm still not 100% confident with it but it seems to be ok as have tried a few test cases against it using the production data.

Apologies, I didn't give enough information about the data in the first place. cola is a sequence associated with id and will be 1,2,3 except when there's an A and a B together which will cause it to repeat like 1,2,2,3 . colb will always be 1 for null and A and 2 for B.

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, NULL 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 colb, rn) as rn2
    from   (select   id, cola, to_number ((cola + colb)||colb) as 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

Chris Saxon
October 23, 2017 - 4:57 pm UTC

OK. So I don't understand the reasoning behind this rule:

3333,3,1,A should appear before 3333,2,2,B

Could you clarify?

Odd rules

Richard Shaw, October 23, 2017 - 11:31 pm UTC

Unfortunately the A and Bs are not recorded as a pair when there's a repeat sequence for cola so need to also take into account the letter for cola-1 as well as cola+1 for the reordering as in some cases the matching A or B has been placed one increment up or down due to the way its being recorded. They will never be more than 1 away from each other.

Apologies its been a bit confusing as its been a bit of a struggle to make up an example for the real data.
Chris Saxon
October 24, 2017 - 8:00 am UTC

Defining this rule is key to getting your sort correct! Once you've nailed down exactly what it is, defining the sort is (relatively) easy.

Of course, it could be that the "rule" isn't always followed...

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.