21

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
p.campbell
  • 98,673
  • 67
  • 256
  • 322
Evik James
  • 10,335
  • 18
  • 71
  • 122

2 Answers2

17

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 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. – Evik James Nov 05 '11 at 19:32
  • 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 – Andomar Nov 05 '11 at 19:43
  • Actually, there will only be two levels, parent and child. Ther will not be grandparent or grandchildren. – Evik James Nov 05 '11 at 21:02
  • Right, for two levels the solution is much simpler! See edited answer. – Andomar Nov 05 '11 at 21:09
10

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
Andomar
  • 232,371
  • 49
  • 380
  • 404
You Qi
  • 8,353
  • 8
  • 50
  • 68
  • 1
    +1 This works nicely as long as the table is in alfabetical order, and you don't have more than 999 rows – Andomar Nov 05 '11 at 21:11
  • 3
    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. – Seph Nov 06 '11 at 08:34