How to sort Oracle SQL

Question

Please tell me this problem.

label   |   code   |   datetime   |
A         |   1          |   11/20 00:00|
A         |   0          |   11/20 00:05|
A         |   1          |   11/20 00:06|
A         |   1          |   11/20 00:07|
A         |   0          |   11/20 00:10|
A         |   0          |   11/20 00:12|

I want to sort it like this.

label   |   code   |   datetime   |
A         |   1          |   11/20 00:00|
A         |   0          |   11/20 00:05|
A         |   1          |   11/20 00:06|
A         |   0          |   11/20 00:10|
A         |   1          |   11/20 00:07|
A         |   0          |   11/20 00:12|

The key is the code and datetime columns, I want to line up the code columns 1,0,1,0.

Marmite Bomber · Accepted Answer · 2020-11-13 20:13:37Z

3

First add an index of the code using the row_number function identifying the relative order of the zeroes and once.

select code, 
row_number() over (partition by code  order by date_time) code_idx,
date_time
from tab

      CODE   CODE_IDX DATE_TIME          
---------- ---------- -------------------
         0          1 13.11.2020 00:00:05
         0          2 13.11.2020 00:00:12
         0          3 13.11.2020 00:00:15
         1          1 13.11.2020 00:00:00
         1          2 13.11.2020 00:00:06
         1          3 13.11.2020 00:00:07
         1          4 13.11.2020 00:00:10

Then simple sort first on the index and than on the code

with tab2 as ( 
select code, 
row_number() over (partition by code  order by date_time) code_idx,
date_time
from tab)
select 
  code, date_time
from tab2
order by code_idx, code desc;

      CODE DATE_TIME          
---------- -------------------
         1 13.11.2020 00:00:00
         0 13.11.2020 00:00:05
         1 13.11.2020 00:00:06
         0 13.11.2020 00:00:12
         1 13.11.2020 00:00:07
         0 13.11.2020 00:00:15
         1 13.11.2020 00:00:10

This will of course fail if you have a different number of 0and 1 (more that one difference in the count).

edited Nov 13, 2020 at 20:13

answered Nov 13, 2020 at 13:04

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piyo_lune Over a year ago

Thanks for the answer! I will try this method immediately :)

Collectives™ on Stack Overflow

How to sort Oracle SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related