How do I order by parent then child?

Question

I am trying to write my SQL Server 2008 query in such a way that I can just loop through my output and output headers as needed. I've done this stuff the wrong way many times and had ColdFusion do the hard work within the page, but need this done in SQL Server.

FeatureID ParentID Feature
--------------------------
1         0        Apple      
2         0        Boy 
3         2        Charles
4         1        Daddy
5         2        Envelope
6         1        Frankfurter

I want my query resultset to look like this:

FeatureID ParentID Feature
--------------------------
1         0        Apple      
4         1        Daddy
6         1        Frankfurter
2         0        Boy 
3         2        Charles
5         2        Envelope

If the ParentID is 0, it means that it's a major category. If the ParentID is greater than 0, it means it's a minor category, a child of the parent.

So the parents need to be ordered A - Z and the children need to be ordered A-Z.

Can you help me get this ordered correctly?

SELECT FeatureID, ParentID, Feature
FROM Features
ORDER BY

Solution 1

From your comment, if you know there are only two levels, there is an easy solution:

select  *
from    @Features feat
order by
        case 
        when ParentID = 0 
        then Feature 
        else    (
                select  Feature 
                from    @Features parent 
                where   parent.FeatureID = feat.ParentID
                ) 
        end
,       case when ParentID = 0 then 1 end desc
,       Feature

1. Sort by the name of the root element: for the root, this is Feature column. For the children, look up the root's name with a subquery.
2. Sort the root on top
3. Sort the children by name

Example at SE Data.

Solution 2

for mysql, you can try: (with the condition your Child's ParentID is your ParentID's FeatureID)

SELECT FeatureID, ParentID, Feature
FROM Features
ORDER BY case when ParentID=0 then FeatureID else ParentID end * 1000 + FeatureID ASC

Comments

[Evik James]

Jeepers!!! That's a lot of code for something that seems like it should be a LOT more straightforward. Your example seems to work fine. This is more cumbersome than doing it a different way.

[Andomar]

Yeah, way to complicated for production code. I was hoping someone else would post a simpler solution. Note that you could greatly simplify the code if you were limited to a certain depth, say max 2 nodes between root and leaf

[Evik James]

Actually, there will only be two levels, parent and child. Ther will not be grandparent or grandchildren.

[Andomar]

Right, for two levels the solution is much simpler! See edited answer.

[Andomar]

+1 This works nicely as long as the table is in alfabetical order, and you don't have more than 999 rows

[Seph]

Shouldn't this just be ORDER BY CASE WHEN ParentID=0 THEN FeatureID ELSE ParentID END ASC, FeatureID ASC instead? Because then it will still use indexes and also it will not be limited to 999 records. But yes this fails if a you have (7, 8, 'bar') and (8, 0, 'foo'), (SELECT COUNT(1) FROM Features WHERE FeatureID < ParentID) must = 0. meaning an item can never have a FeatureID less than it's parent.