persons where the children are grouped for their parent

Question

I have an SQLite database with a simple table of Persons.

Each row represents a person and has four columns: the primary key (id), one for the name, one for the age and one for the parent (points to the primkey id, is NULL if the person has no parent).

Now, I just want to list all those persons sorted on age and have the children grouped with their parent.

For example: (Name, Age)

Dan, 18
Eva, 21
- Bill, 4
- Bull, 7

Steve, 38
Richard, 63
- Ann, 19
- Clara, 39

Vera, 70  

This sounds simple, but I cannot figure out the SQL statement :-(
Thanks in advance!

/John

Answer 1

In case you need only one level of depth, you can do it like this:

SELECT  CONCAT(CASE WHEN parent IS NOT NULL THEN '- ' ELSE '' END, name), age
FROM    table
ORDER BY
        CASE WHEN parent IS NOT NULL THEN parent ELSE id END, parent IS NOT NULL

If you want to build the whole hierarchy, it's harder.

Update: Initially, the question tag said MySQL, so the answer is relevant to MySQL.

I'll leave the answer, but it won't work for SQLite.

MySQL does not have native support for this, so you'll need to create some special functions.

See this article in my blog for details:

• Hierarchical queries in MySQL

In a nutshell:

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    t_hierarchy
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    t_hierarchy
                WHERE   id = _parent;
        END LOOP;
END

SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, parent, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id

Answer 2

I recommend you to split this into two queries.

First, get a list of parents:

SELECT *
FROM Persons
WHERE id IN (SELECT parent FROM Persons)
ORDER BY (age, id)

Then get a properly sorted list of children:

SELECT Child.*
FROM Persons AS Child
     JOIN Persons AS Parent ON (Parent.id = Child.parent)
ORDER BY (Parent.age, Parent.id, Child.age, Child.id)

The two lists can then easily be merged on the id/parent since they are both sorted first by parent's age.