CODEWITHSUNDEEP
sqlt-sql
Evik James
Evik James

Reputation: 10483

How do I order by parent then child?

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

Upvotes: 24

Views: 17770

Answers (2)

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 18

You Qi
You Qi

Reputation: 9211

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

Upvotes: 10

Related Questions