How to order SQL rows in order to create nice looking rollup?

Question

This is my table:

CREATE TABLE t (id INT, parent INT, FOREIGN KEY(parent) REFERENCES t(id));

This is a collection of data I have:

id    parent
1     NULL
2     NULL
3     1
4     1

I would like to select them and order like this:

id    parent
1     NULL
3     1
4     1
2     NULL

I can't find a proper way to do it (in MySQL 5+). Please help, thanks!

Answer 1

If there's only children and parents, and no grandchildren, you can use:

select  id
,       parent
from    yourtable
order by
        coalesce(parent, id)

Answer 2

I believe this should do the trick

SELECT id, parent FROM t ORDER BY IF(parent is NOT NULL,parent,id)

Answer 3

Not possible in a simple query, you have to order by 1 column at a time. I haven't tried it with MySQL. It's possible if you joined an ordered query with another ordered query you might be able to do something, but I doubt you could ever guarantee the order across DB versions or different data sets.

It's likely that the easier option would be to just deal with it in a specific order from the DB and display it in the order you want through your front end code.

Answer 4

SELECT * FROM REFERENCES ORDER BY id=2, parent , id

OR JUST

SELECT * FROM REFERENCES ORDER BY id=2 , id