SQL query order by top category follow by subcategory

i have 1 category table which has parent_id field in it.. parent_id can contain category id so we know its sub catgeory.
parent_id = 0 means it is top level category

i want to write a single query to order by top category first, then all subcategories of that top category, then all sub-subcategories of that top category. does any one has any idea on this.

  • SQL Server and C#: get last inserted id
  • Decimal numbers in sql server
  • Find closest date in SQL Server
  • Large Query Timing Out in ASP.Net. Optimization strategy?
  • Dynamic pivot with Max(Date) from pivot
  • How to get last 7 days data from current datetime to last 7 days in sql server
  • Thanks

    Sampe data

    CategoryId      |   CategoryName    |   Parent_id
    1                   cat A               0
    2                   cat B               0
    3                   cat C               0
    4                   cat D               0
    5                   cat A A             1
    6                   cat A B             1
    7                   cat A A A           5
    8                   cat A A B           5
    9                   cat B A             2
    10                  cat B B             2
    11                  cat B C             2
    12                  cat B D             2
    

    The result should be like:

    CategoryId      |   CategoryName    |   Parent_id
    1                   cat A               0
    5                   cat A A             1
    7                   cat A A A           5
    8                   cat A A B           5
    6                   cat A B             1
    2                   cat B               0
    9                   cat B A             2
    10                  cat B B             2
    11                  cat B C             2
    12                  cat B D             2
    3                   cat C               0
    4                   cat D               0
    

    One Solution collect form web for “SQL query order by top category follow by subcategory”

    Declare @YourTable table (categoryID int,CategoryName varchar(50),parent_id int)
    Insert into @YourTable values 
    (1 ,'cat A',    0),
    (2 ,'cat B',    0),
    (3 ,'cat C',    0),
    (4 ,'cat D',    0),
    (5 ,'cat A A',  1),
    (6 ,'cat A B',  1),
    (7 ,'cat A A A',5),
    (8 ,'cat A A B',5),
    (9 ,'cat B A',  2),
    (10,'cat B B',  2),
    (11,'cat B C',  2),
    (12,'cat B D',  2)
    
    Declare @Top  int = null             --<<  Sets top of Hier Try 5
    Declare @Nest varchar(25) ='|-----'  --<<  Optional: Added for readability
    
    ;with ctePt as (
          Select Seq  = cast(1000+Row_Number() over (Order by CategoryName) as varchar(500))
                ,categoryID
                ,parent_id
                ,Lvl=1
                ,CategoryName 
          From   @YourTable 
          Where  IsNull(@Top,0) = case when @Top is null then isnull(parent_id,0) else categoryID end
          Union  All
          Select Seq  = cast(concat(p.Seq,'.',100000+Row_Number() over (Order by r.CategoryName)) as varchar(500))
                ,r.categoryID
                ,r.parent_id,p.Lvl+1
                ,r.CategoryName 
          From   @YourTable r 
          Join   ctePt p  on r.parent_id = p.categoryID)
         ,cteR1 as (Select Seq,categoryID,R1=Row_Number() over (Order By Seq) From ctePt)
         ,cteR2 as (Select A.Seq,A.categoryID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.categoryID )
    Select B.R1  
          ,C.R2
          ,A.categoryID
          ,A.parent_id
          ,A.Lvl
          ,CategoryName = Replicate(@Nest,A.Lvl-1) + A.CategoryName
     From ctePt A
     Join cteR1 B on A.categoryID=B.categoryID
     Join cteR2 C on A.categoryID=C.categoryID
     Order By B.R1
    

    Returns

    enter image description here

    Now, you may notice R1/R2. These are the Range Keys. I find them useful in navigating and aggregated data up the hierarchy. If you don’t need them, remove cteR1 and cteR2 and set the final Order By A.Seq

    Just for fun, if you set @Top=5, you’ll get

    enter image description here

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.