SQL Statement to order rows by so children follow each parent

Question

I have a category table with id,name,parentid.

id------name------parentid
1        a          0
2        b          0
3        c          1
4        d          1
5        e          2  
6        f          2     
7        g          2
8        h          2   
9        i          0

I want a single query to display the result in the following format:

1---a
3---c
4---d
2---b
5---e
6---f

Answer 1

Perhaps you want unique pairs of id and name.

SELECT unique(id, name) FROM category;

Answer 2

OK this will only work if you've less than 10,000 children per parent, and will only work for two levels i.e. parent has child, but not child has child.

Also, this is general SQL, don't know if it'll work for mysql

select id, name from Category
order by (parentid * 10000) + id

Hope this helps

Answer 3

You'll need to write a function to do it.

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

and use it in a query:

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

See this entry it my blog for more detail on how it works:

• Hierarchical queries in MySQL