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.

Answer 1

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