CODEWITHSUNDEEP
sqlpostgresql
fksg0001 fksg0001
fksg0001 fksg0001

Reputation: 43

SQL, How to order the result of a query with precedence constraints

I have two tables with a relation 1-n. I'm looking for the way to order my elements. I could use a column "position(int)" but i prefer to use the id of the precedence :

+-------+---------------+-------+
| pk_id | precedence_id | fk_id |
+-------+---------------+-------+
|     4 |             1 |    10 |
+-------+---------------+-------+
|     1 |             6 |    10 |
+-------+---------------+-------+
|     2 |             5 |    40 |
+-------+---------------+-------+
|     3 |          NULL |    10 |
+-------+---------------+-------+
|     6 |             3 |    10 |
+-------+---------------+-------+
|     5 |          NULL |    40 |
+-------+---------------+-------+

I have a primary key (pk_id), a foreign key (fk_id) and a precedence constraint on the same table (precedence_id).

I am looking for the query to get the result with the precedence :

+-------+---------------+-------+
| pk_id | precedence_id | fk_id |
+-------+---------------+-------+
|     3 |          NULL |    10 |
+-------+---------------+-------+
|     6 |             3 |    10 |
+-------+---------------+-------+
|     1 |             6 |    10 |
+-------+---------------+-------+
|     4 |             1 |    10 |
+-------+---------------+-------+
|     5 |          NULL |    40 |
+-------+---------------+-------+
|     2 |             5 |    40 |
+-------+---------------+-------+

SELECT * 
FROM tb 
ORDER BY fk_id, ??

Upvotes: 0

Views: 104

Answers (3)

fksg0001 fksg0001
fksg0001 fksg0001

Reputation: 43

This is working properly :

WITH RECURSIVE recursive(pk_id, precedence_id, position) AS (
    SELECT pk_id, precedence_id, 0
    FROM tb
    WHERE precedence_id ISNULL

    UNION ALL

    SELECT v.pk_id, v.precedence_id, rec.position + 1
    FROM
        tb v
        INNER JOIN recursive rec ON rec.pk_id = v.precedence_id
)
SELECT tst.*, rec.position
FROM
    recursive rec
    INNER JOIN tb tst ON rec.pk_id = tst.pk_id
ORDER BY tst.fk_id, rec.position;

Upvotes: 1

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

with  recursive t (pk_id,path,L_name) as (

 select  pk_id
        ,array[pk_id]
        ,coalesce(precedence_id,0) as L_tree -- can be representative relation_name 
        ,precedence_id
        ,fk_id
   from tb 
  where coalesce(precedence_id,0) = 0 

union all 

 select  el.pk_id
        ,t.path || array[el.pk_id]
        ,coalesce(el.precedence_id,0) as L_tree -- can be representative relation_name 
        ,coalesce(el.precedence_id,0) as precedence_id
        ,el.fk_id

   from tb as el 
   join t on (el.precedence_id= t.pk_id)
  where el.pk_id <> any (t.path))

 select pk_id
       ,cast(case when array_upper(path,1)>1 then '    ' else '' end || repeat('    ', array_upper(path,1)-2)||L_name  as character varying(1000)) as L_tree
       ,precedence_id
       ,array_upper(path,1) as tree_level
       ,fk_id  from t 

 order by path

Try using recursive with. I dug this up from one of my old projects.

Edit: Found a similar example hierarchical data in postgres

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use precedence_id in order by clause 

select * from tb
order by fk_id,precedence_id desc,pk_id

http://sqlfiddle.com/#!17/ba4b8/5

Upvotes: 0

Related Questions