locked
nested order by RRS feed

  • Question

  • Could you please help me about the counterpart SQL server solution that mentioned in the link below for mysql:

    http://stackoverflow.com/questions/8864397/sql-nested-order-by/32049258#32049258

    Monday, August 17, 2015 11:41 AM

Answers

  • Hi Aminlzadpanah,

    What about a recursive CTE way to get the expected order? Please see below.

    DECLARE @T TABLE(category_id INT,category_name VARCHAR(10),parent_id INT,display_order INT)
    
    INSERT INTO @T VALUES
    (1 ,'A',  0, 0),      
    (2 ,'a1', 1, 0),      
    (3 ,'a2', 1, 1),
    (4 ,'a3', 1, 2),
    (5 ,'B',  0, 1),      
    (6 ,'b1', 5, 0),      
    (7 ,'b2', 5, 1),
    (8 ,'b3', 5, 2)
    
    SELECT category_id,category_name,parent_id,display_order FROM @T WHERE parent_id=0
     
    ;WITH Cte AS
    (
    SELECT 
    category_id,category_name,parent_id,display_order,-1 display_order2 FROM @T WHERE parent_id=0
    UNION ALL
    SELECT T.category_id,T.category_name,T.parent_id,C.display_order,T.display_order FROM @T T INNER JOIN Cte C ON T.parent_id=C.category_id
    )
    SELECT category_id,category_name,parent_id,display_order FROM Cte ORDER BY display_order,display_order2

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by Naomi N Tuesday, August 18, 2015 12:08 PM
    • Marked as answer by Charlie Liao Wednesday, September 9, 2015 8:07 AM
    Tuesday, August 18, 2015 4:55 AM

All replies

  • ORDER BY CASE WHEN x=1 THEN y ELSE z END

    Monday, August 17, 2015 11:47 AM
  • Hi,

    Please check the below link

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb1ac20f-294c-4746-bc5e-db1771ae085b/nested-case-in-order-by?forum=transactsql


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Monday, August 17, 2015 12:43 PM
  • ORDER BY CASE WHEN x=1 THEN y ELSE z END

    Thanks. But in the link within my question, there isn't any case and condition. Just by using the statement "ORDER BY IF(parent_id, parent_id, category_id), parent_id, display_order" all of the records are nested ordered by the specified grouping columns.
    Tuesday, August 18, 2015 3:50 AM
  • Hi,

    Please check the below link

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb1ac20f-294c-4746-bc5e-db1771ae085b/nested-case-in-order-by?forum=transactsql


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das


    Thanks. But in the link within my question, there isn't any case and condition. Just by using the statement "ORDER BY IF(parent_id, parent_id, category_id), parent_id, display_order" all of the records are nested ordered by the specified grouping columns.
    Tuesday, August 18, 2015 3:50 AM
  • Can you post your table structure, input statements and desired output? I think in your case you would need a LEFT JOIN first. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 18, 2015 4:06 AM
  • Hi Aminlzadpanah,

    What about a recursive CTE way to get the expected order? Please see below.

    DECLARE @T TABLE(category_id INT,category_name VARCHAR(10),parent_id INT,display_order INT)
    
    INSERT INTO @T VALUES
    (1 ,'A',  0, 0),      
    (2 ,'a1', 1, 0),      
    (3 ,'a2', 1, 1),
    (4 ,'a3', 1, 2),
    (5 ,'B',  0, 1),      
    (6 ,'b1', 5, 0),      
    (7 ,'b2', 5, 1),
    (8 ,'b3', 5, 2)
    
    SELECT category_id,category_name,parent_id,display_order FROM @T WHERE parent_id=0
     
    ;WITH Cte AS
    (
    SELECT 
    category_id,category_name,parent_id,display_order,-1 display_order2 FROM @T WHERE parent_id=0
    UNION ALL
    SELECT T.category_id,T.category_name,T.parent_id,C.display_order,T.display_order FROM @T T INNER JOIN Cte C ON T.parent_id=C.category_id
    )
    SELECT category_id,category_name,parent_id,display_order FROM Cte ORDER BY display_order,display_order2

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Proposed as answer by Naomi N Tuesday, August 18, 2015 12:08 PM
    • Marked as answer by Charlie Liao Wednesday, September 9, 2015 8:07 AM
    Tuesday, August 18, 2015 4:55 AM
  • There is no IF expression in T-SQL. Use CASE (as already suggested) or IIF (if 2012 or later).

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, August 18, 2015 8:18 PM