How to order by name, then by parent ID

• Paul8112

  SSC Enthusiast

  Points: 154

  More actions

  February 12, 2009 at 6:53 am

  #126377

  It's been a while since I've done any TSQL and seem to be stuck at something simple. I have the following table definition:

  CategoryID,

  CategoryName,

  ParentCategoryID

  How do I order the results by CategoryName, then by ParentCategoryID?

  So my data would probably look something like this:

  CatID CatName ParentCatID

  1 Parent 1 NULL

  2 Child 1 1

  3 Child 2 1

  5 Child 3 1

  4 Parent 2 NULL

  6 Child 1 4

  7 Child 2 4

  I have mixed the order of CatID on purpose, as the order should always be CatName, ParentCatID.

  Ideally, I'd like the CatName to be in alphabetical order for the Child categories as well...

  Thanks,

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 12, 2009 at 7:02 am

  #942815

  Untested, but try this

  WITH CTE(CategoryID,ParentCategoryID,CategoryName,fullpath) AS (

  SELECT CategoryID,ParentCategoryID,CategoryName,CAST(CategoryName AS VARCHAR(MAX))

  FROM mytable

  WHERE ParentCategoryID IS NULL

  UNION ALL

  SELECT p.CategoryID , p.ParentCategoryID ,p.CategoryName, c.fullpath+'/'+CAST(p.CategoryName AS VARCHAR(MAX))

  FROM CTE c

  INNER JOIN mytable p ON p.ParentCategoryID=c.CategoryID)

  SELECT CategoryID,CategoryName,ParentCategoryID

  FROM CTE

  ORDER BY fullpath

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Paul8112

  SSC Enthusiast

  Points: 154

  More actions

  February 12, 2009 at 7:07 am

  #942822

  Works perfectly, thanks.

  Think this will go in a stored procedure rather than trying to figure out how to write this in LINQ...