Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: October 09, 2015 - 2:16 pm UTC

Last updated: October 09, 2015 - 11:28 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello

I have a table that looks like the below

id class sequence

126A 7000 1
127B 7000 2
130A 7000 3
130B 7000 4
125B 6000 5
126A 6000 6
127B 6000 7
130A 6000 8


I would like the results to have a different sequence in which I get them ordered by id but also sequence.

If I order by ID, and sequence I get the following

125B 6000 5
126A 7000 1
126A 6000 6
127B 7000 2
127B 6000 7
130A 7000 3
130A 6000 8
130B 7000 4

But what I would like is to respect the sequence as well in order to get something like that

126A 7000 1
126A 6000 6
127B 7000 2
127B 6000 7
130A 7000 3
130A 6000 8
130B 7000 4
125B 6000 5

I have been trying with min() and group by but I can't get what I want.

The logic is to follow the sequence order, and within the sequence order group the ID, so for instance

sequence 1 is ID 126A, so I want all records with 126A, ordered by the sequence

126A 7000 1
126A 6000 6


the next is sequence 2, which is ID 127B, so we would have

127B 7000 2
127B 6000 7

then comes the ID linked to sequence 3,

130A 7000 3
130A 6000 8

and so on.


I appreciate any help

Thanks a lot
John

and Chris said...

Find the minimum sequence number for each id. You can do this using analytics.

You can then order by this, then the sequence number:

create table t (id varchar2(5), class integer, sequence int);

insert into t values ('126A', 7000, 1);
insert into t values ('127B', 7000, 2);
insert into t values ('130A', 7000, 3);
insert into t values ('130B', 7000, 4);
insert into t values ('125B', 6000, 5);
insert into t values ('126A', 6000, 6);
insert into t values ('127B', 6000, 7);
insert into t values ('130A', 6000, 8);
commit;

select * from (
  select t.*, 
         min(sequence) over (partition by id) mn
  from   t
)
order  by mn, sequence;

ID         CLASS   SEQUENCE         MN
----- ---------- ---------- ----------
126A        7000          1          1
126A        6000          6          1
127B        7000          2          2
127B        6000          7          2
130A        7000          3          3
130A        6000          8          3
130B        7000          4          4
125B        6000          5          5

 8 rows selected 


You can simplify this to:

select t.*
from   t
order  by min(sequence) over (partition by id) , sequence

ID         CLASS   SEQUENCE
----- ---------- ----------
126A        7000          1
126A        6000          6
127B        7000          2
127B        6000          7
130A        7000          3
130A        6000          8
130B        7000          4
125B        6000          5

 8 rows selected 

Rating

  (2 ratings)

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

Comments

John Lime, October 09, 2015 - 4:12 pm UTC

That's brilliant

I did not know I could use the over partition by in a order by clause


Thanks a lot!

John Lime, October 09, 2015 - 4:48 pm UTC

Hello Chris,

Is there any other way of rewriting the query without the analytic function? The reason is that Oracle forms do not support it.

Many thanks
Connor McDonald
October 09, 2015 - 11:28 pm UTC

You could dynamic SQL or put the analytic into a database view (just be careful when you do that, it may have performance implications).

Connor

More to Explore

Analytics

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