2

I have a table with few records having a parent record as shown below. Not all of them have parent records.

id | parent_id
--------------
1 |  0
2 |  0
3 |  1
4 |  0
5 |  0
6 |  0
7 |  5

I would like the records to be ordered with parent followed by child:

id | parent_id
--------------
1 |  0
3 |  1
2 |  0
4 |  0
5 |  0
7 |  5
6 |  0

How can I achieve this using a SQL Query WITHOUT using stored procedures?

I am using postgres.

m.beginner
  • 389
  • 2
  • 18
  • 1
    Unrelated, but: storing a `0` instead of `null` to indicate "no parent" is a bad idea. This prevents you from using proper foreign key constraints –  Aug 02 '19 at 09:33
  • Your example suggests exactly 1 level of hierachchy. is that so? And properly enforced? – Erwin Brandstetter Aug 02 '19 at 12:04

4 Answers4

2

You need a recursive query that carries the root ID through all levels, then you can sort the rows by that:

with recursive entries as (
  select id, parent_id, id as root_id, 1 as level
  from the_table
  where parent_id = 0 -- this should be IS NULL
  union all 
  select c.id, c.parent_id, p.root_id, p.level + 1
  from the_table c
    join entries p on p.id = c.parent_id
)
select id, parent_id
from entries
order by root_id, level, id;

Online example: https://rextester.com/YKUJ56922

  • This does not give me the result that I want (the one I published in my original question) – m.beginner Aug 02 '19 at 10:44
  • @m.beginner: the online example returns exactly the result you have listed in your question. –  Aug 02 '19 at 10:47
  • This produces all records with parent id as 0 first and then prints the records with proper parent id and child linking. I don't want to dump all records with parent id as 0 first – m.beginner Aug 02 '19 at 11:18
  • Again: this returns **exactly** what you have shown as the desired output in your question. If that is not what you want, you need to update your question to show what you really want. –  Aug 02 '19 at 11:20
  • my apologies; not sure what I did wrong the first time; this DOES give the result I want. One question though - I guess this creates a temp table named `entries`? Is it possible to achieve this without creating a temp table? – m.beginner Aug 02 '19 at 11:29
  • No, it does not create a temp table. And no it's not possible to do a recursive query without a [common table expression](https://www.postgresql.org/docs/current/queries-with.html) –  Aug 02 '19 at 11:30
  • 1
    This only *seems* to implement *"parent followed by child"* because the test case of the OP is constructed poorly, with always `parent.id < child.id`. Fails with `parent.id > child.id`. – Erwin Brandstetter Aug 02 '19 at 12:31
  • @ErwinBrandstetter: good point. It can be amended by adding a `level` column and include that in the `order by` see: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=af010ba80655dda559f20c3a401f60a7 - but if it's only a single level hierarchy, your solution would be much better to begin with. –  Aug 02 '19 at 12:34
  • I have only a single level hierarchy. Any parent would have only 1 child. So am thinking maybe `recursive` is an overkill? – m.beginner Aug 02 '19 at 12:36
  • @m.beginner: yes, in that case recursive is not needed. Erwin's answer is most probably a lot more efficient then. –  Aug 02 '19 at 12:37
2

Assuming there is exactly one level of hierarchy possible like your example suggests:

SELECT child.*
FROM   tbl      AS child
LEFT   JOIN tbl AS parent ON parent.id = child.parent_id
ORDER  BY COALESCE(parent.id, child.id)  -- order by parent if exists
        , parent.id IS NOT NULL          -- parent first per group
        , child.id;                      -- order rest by id

The join is only needed if we sort by some additional attribute like a "name" (which is the typical case as values of surrogate IDs have no meaning). While only sorting by ID like you demonstrate, we don't need the join as all information is already there (like also demonstrated by Gordon). Then we can simplify:

SELECT *
FROM   tbl
ORDER  BY CASE WHEN parent_id = 0 THEN id ELSE parent_id END
        , parent_id <> 0
        , id;

db<>fiddle here - with extended test case to demonstrate relevance of ORDER BY items.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

I think you want:

order by coalesce(nullif(parent_id, 0), id), id

Basically, ignore the zeros in parent_id. Then use the parent_id if present, otherwise use id.

Here is a db<>fiddle.

This version assumes that parent ids are smaller than child ids -- which is true in your data and makes sense in most cases. If you want to be explicit about the ordering:

order by coalesce(nullif(parent_id, 0), id),
         (parent_id = 0) desc,
         id
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This was VERY useful and EXTREMELY simple. However this also produces the same result the RECURSIVE query suggested below. This dumps all records with parent id as 0 first and then produces the list of records with correct parent-child order. However I don't want to list all records with source_id 0 first – m.beginner Aug 02 '19 at 11:01
  • @m.beginner . . . It most certainly *does not*. It does exactly what your question asks for. I added a db<>fiddle to demonstrate this. – Gordon Linoff Aug 02 '19 at 12:27
  • Smart, but still fails to sort parents before their children. – Erwin Brandstetter Aug 02 '19 at 12:51
  • @ErwinBrandstetter . . . It works for the provided data and I think that having the parent defined before the child generally makes sense. However, I edited the answer in case the sample data is not relevant. – Gordon Linoff Aug 02 '19 at 12:57
  • @Gordon: Yes, creating parent before child seems to make sense. But doesn't have to be so while there is no FK (and not even then if it's deferrable). Also, IDs still don't have to be smaller just because the row has been added first. Not even with a `serial` column. – Erwin Brandstetter Aug 02 '19 at 13:04
-1

I use this way (Postgres):

SELECT id, parent_id, name 
FROM   my_table 
ORDER BY 
COALESCE(parent_id,id)||id::varchar
Nino
  • 6,931
  • 2
  • 27
  • 42
Bighamster
  • 111
  • 1
  • 2