CODEWITHSUNDEEP
mysqlsql
Comum
Comum

Reputation: 453

Ordering rows with id parameter

So I have a table in a MariaDB database with several rows like:

+----+--------+-----------+---------+-----------+
| id | faseID | fase_tipo | fase_nr | tarefa_id |
+----+--------+-----------+---------+-----------+
|  5 |      3 |         2 |       1 |         2 |
|  6 |      3 |         2 |       2 |         2 |
| 17 |      3 |         2 |       3 |         2 |
| 12 |      3 |         3 |       1 |         6 |
| 18 |      3 |         3 |       2 |         6 |
+----+--------+-----------+---------+-----------+

Generated by:

SELECT id, 
       faseID, 
       fase_tipo, 
       fase_nr, 
       tarefa_id 
   FROM tarefas 
   WHERE obra = '15ID000' AND 
         faseID = '3' AND 
         tarefa_id <> '0' AND 
         tarefa_main = '2'
   ORDER BY fase_tipo ASC

I'm having trouble ordering this search result, because I want to have the table ordered as: 

+----+--------+-----------+---------+-----------+
| id | faseID | fase_tipo | fase_nr | tarefa_id |
+----+--------+-----------+---------+-----------+
|  5 |      3 |         2 |       1 |         2 |
|  6 |      3 |         2 |       2 |         2 |
| 12 |      3 |         3 |       1 |         6 |
| 18 |      3 |         3 |       2 |         6 |
| 17 |      3 |         2 |       3 |         2 |
+----+--------+-----------+---------+-----------+

What I mean is, use the field tarefa_id to make the rows appear after the row with that id. And inside that have it ordered with fase_nr.

Is there a any to target all the rows with tarefa_id to appear after id = tarefa_id?

Upvotes: 0

Views: 59

Answers (1)

gmiley
gmiley

Reputation: 6604

Take a look at the below, it should give you what you want by using a self join and the coalesce function.

    with test_data as (
        select 5 as id, 3 as faseID, 2 as fase_tipo, 1 as fase_nr, 2 as tarefa_id from dual
        union all
        select 6 as id, 3 as faseID, 2 as fase_tipo, 2 as fase_nr, 2 as tarefa_id from dual
        union all
        select 12 as id, 3 as faseID, 3 as fase_tipo, 1 as fase_nr, 6 as tarefa_id from dual
        union all
        select 18 as id, 3 as faseID, 3 as fase_tipo, 2 as fase_nr, 6 as tarefa_id from dual
        union all
        select 17 as id, 3 as faseID, 2 as fase_tipo, 3 as fase_nr, 2 as tarefa_id from dual
    )
    -- This is the core you should pay attention to
    select td1.* 
    from test_data td1
    left join test_data td2 
    on td2.id = td1.tarefa_id
    order by coalesce(td2.id, td1.id), td1.id, td1.fase_nr
    --
;

Granted, I did this in Oracle, but the general idea should apply.

Output:

ID|FASEID|FASE_TIPO|FASE_NR|TAREFA_ID
--+------+---------+-------+---------
 5|     3|        2|      1|        2
 6|     3|        2|      2|        2
12|     3|        3|      1|        6
18|     3|        3|      2|        6
17|     3|        2|      3|        2

If your columns are not of a numeric type you will need to convert them in the order by clause for sorting:

with test_data as (
    select '12' as id, '3' as faseID, '3' as fase_tipo, '1' as fase_nr, '6' as tarefa_id from dual
    union all
    select '5' as id, '3' as faseID, '2' as fase_tipo, '1' as fase_nr, '2' as tarefa_id from dual
    union all
    select '18' as id, '3' as faseID, '3' as fase_tipo, '2' as fase_nr, '6' as tarefa_id from dual
    union all
    select '6' as id, '3' as faseID, '2' as fase_tipo, '2' as fase_nr, '2' as tarefa_id from dual
    union all
    select '17' as id, '3' as faseID, '2' as fase_tipo, '3' as fase_nr, '2' as tarefa_id from dual
)
-- This is the core you should pay attention to
select td1.* 
from test_data td1
left join test_data td2 
on td2.id = td1.tarefa_id
order by to_number(coalesce(td2.id, td1.id)), to_number(td1.id), to_number(td1.fase_nr)
--
;

Upvotes: 1

Related Questions