SQL Server: Select Parent-Child

Question

Hi

I have SQL Server 2008 with a table called ProductCategories designed like this:

Id | Name      | ParentId
71   PCs         NULL
32   MACs        NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I would like to select from this table, and get a result set like this:

Id | Name      | ParentId
71   PCs         NULL
 3   Keyboard    1
 9   Mouse       1
 5   Screen      1
32   MACs        NULL
11   Keyboard    2
 7   Mouse       2
 8   Screen      2

I tried this, but that obviously gives me the ones with no ParentId first:

WITH Hierarchy
AS
(
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    WHERE 
        T1.parentid IS NULL OR 
        T1.parentid IN (SELECT id from ProductCategories WHERE parentid IS NULL)
    UNION ALL
    SELECT 
        T1.Id, T1.ParentId
    FROM
        ProductCategories T1
    INNER JOIN 
        Hierarchy TH ON TH.Id = T1.ParentId
)
select *
from Hierarchy 
order by parentid

Please help me, if you can :)

-- The guy who doesn't know SQL

Answer 1

Try this

SELECT id, name, parentId 
FROM categories
ORDER BY ISNULL(parentId,id), id

Btw, shouldn't first two indexes in your table be 1 and 2, not 71 and 32 ?

Answer 2

try this:

Select Id, Name, ParentId
From ProductCategories
Order By Coalesce(ParentId, Id), 
   Coalesce(ParentId, 0), Name

Three Order By clauses,

1. Coalesce(ParentId, Id): This one groups the records by the parent, for both the parent itself and all the children of that parent
2. Coalesce(ParentId, 0) This groups within each set so that the one record with a null parent (the parent) sorts to the top within the group
3. Name, This sorts the children within the group by name