SQL - Ordering by multiple criteria

Answer 1

Not entirely sure of your questions but it sounds like PARTITION BY might be useful for you. There's a good introductory post on PARTITION BY here.

Answer 2

It seems to me like you are looking to flatten and order your hierarchy, the cheapest way to get this ordering would be to store an additional column in the table that has the full path.

So for example:

Name            | Full Path
Free Stuff      | Free Stuff 
aa2             | Free Stuff - aa2            

Once you store the full path, you can order on it.

If you only have a depth of one you can auto generate a string to this effect with a single subquery (and order on it), but this solution does not work that easily when it gets deep.

Another option, is to move this all over to a temp table and calculate the full path there, on demand. But it is fairly expensive.

Answer 3

You could make the table look at itself, ordering by the parent Name then the child Name.

select   categories.Name AS DisplayName
from     categories LEFT OUTER JOIN
         categories AS parentTable ON categories.Parent = parentTable.ID
order by parentTable.Name, DisplayName

Answer 4

Ok, here we go :

with foo as
(
select 1 as id, null as parent, 'CatA' as cat from dual
union select 2, null, 'CatB' from dual
union select 3, null, 'CatC' from dual
union select 4, 1, 'SubCatA_1' from dual
union select 5, 1, 'SubCatA_2' from dual
union select 6, 2, 'SubCatB_1' from dual
union select 7, 2, 'SubCatB_2' from dual
)
select child.cat
from foo parent right outer join foo child on parent.id = child.parent
order by case when parent.id is not null then parent.cat else child.cat end,
         case when parent.id is not null then 1 else 0 end

Result :

CatA
SubCatA_1
SubCatA_2
CatB
SubCatB_1
SubCatB_2
CatC

Edit - Solution change inspire from van's order by ! Much simpler that way.

Answer 5

Here you have a complete working example using a resursive common table expression.

DECLARE @categories TABLE
(
    ID INT NOT NULL,
    [Name] VARCHAR(50),
    Parent INT NULL
);

INSERT INTO @categories VALUES (4,  'Free Stuff', NULL);
INSERT INTO @categories VALUES (1,  'Hardware', NULL);
INSERT INTO @categories VALUES (3,  'Movies', NULL);
INSERT INTO @categories VALUES (2,  'Software', NULL);
INSERT INTO @categories VALUES (10, 'a', 0);
INSERT INTO @categories VALUES (12, 'apples', 2);
INSERT INTO @categories VALUES (8,  'catD', 2);
INSERT INTO @categories VALUES (9,  'catE', 2);
INSERT INTO @categories VALUES (5,  'catA', 3);
INSERT INTO @categories VALUES (6,  'catB', 3);
INSERT INTO @categories VALUES (7,  'catC', 3);
INSERT INTO @categories VALUES (11, 'aa2', 4);

WITH categories(ID, Name, Parent, HierarchicalName)
AS
(
    SELECT
        c.ID
        , c.[Name]
        , c.Parent
        , CAST(c.[Name] AS VARCHAR(200)) AS HierarchicalName
    FROM @categories c
    WHERE c.Parent IS NULL

    UNION ALL

    SELECT
        c.ID
        , c.[Name]
        , c.Parent
        , CAST(pc.HierarchicalName + c.[Name] AS VARCHAR(200))
    FROM @categories c
    JOIN categories pc ON c.Parent = pc.ID
)
SELECT c.*
FROM categories c
ORDER BY c.HierarchicalName

Answer 6

SELECT
  ID,
  Name,
  Parent,
  RIGHT(
    '000000000000000' + 
    CASE WHEN Parent IS NULL 
    THEN CONVERT(VARCHAR, Id) 
    ELSE CONVERT(VARCHAR, Parent) 
    END, 15
  )
  + '_' + CASE WHEN Parent IS NULL THEN '0' ELSE '1' END
  + '_' + Name
FROM
  categories
ORDER BY
  4

The long padding is to account for the fact that SQL Server's INT data type goes from 2,147,483,648 through 2,147,483,647.

You can ORDER BY the expression directly, no need to use ORDER BY 4. It was just to show what it is sorting on.

It is worth noting that this expression cannot use any index. This means sorting a large table will be slow.