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.

  • Regex pattern inside SQL Replace function?
  • Reset auto increment
  • SQL query performance statistics messages returned multiple times
  • Update a table from two comma separated parameter as input
  • What causes a TypeLoadException on Migrate.exe for EF6?
  • Should User and Address be in separate tables?
  • 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.