locked
sql order by clasue with case RRS feed

  • Question

  • User351619809 posted

    Hi All,

    I only have four categories in my table. I don't have any other data in my table:

    Tabl1
    
    Col1
    
    Office
    Transportation
    Prod
    Share
    Sports
    
    

    I need to sort them in this order

    Prod
    Share
    Sports
    Transportation
    Office


    How can I achieve the above sorting. I tried doing this, but the below did not work:

     select * from testTable
        order by
    	   case TestName
    	      when 'Prod' then 1
    		  when 'Share' then 2
    		  when 'Sports' then 3
    		  when 'Transportation' then 4
    		  when 'office' then 5
    		end
    

    Friday, November 4, 2016 8:35 PM

Answers

  • User2053451246 posted

    Works fine with that code on my end.

    WITH cte AS (
    	SELECT 'Prod' AS Col1
    	UNION ALL
    	SELECT 'Share' AS Col1
    	UNION ALL
    	SELECT 'Sports' AS Col1
    	UNION ALL
    	SELECT 'Transportation' AS Col1
    	UNION ALL
    	SELECT 'Office' AS Col1
    )
    
    SELECT *
    FROM cte AS t
    ORDER BY
    	CASE Col1
    		WHEN 'Prod' THEN 1
    		WHEN 'Share' THEN 2
    		WHEN 'Sports' THEN 3
    		WHEN 'Transportation' THEN 4
    		WHEN 'office' THEN 5
    	END
    

    Output:

    Prod
    Share
    Sports
    Transportation
    Office

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2016 8:59 PM
  • User77042963 posted

    It should work:

    select Col1 from Tabl1
     order by
    	   case Col1
    	      when 'Prod' then 1
    		  when 'Share' then 2
    		  when 'Sports' then 3
    		  when 'Transportation' then 4
    		  when 'office' then 5
    		end
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2016 9:08 PM

All replies

  • User-654786183 posted

    I can see that you don't want to sort it in alphabetical order asc or desc.  If you want a custom sort order, the solution I would suggest is to have another column added to this table called sequence or order and have integer values in it

    Tabl1

    Col1                            Col2

    Office                              5
    Transportation                4
    Prod                                1          
    Share                              2
    Sports                             3

    Friday, November 4, 2016 8:46 PM
  • User351619809 posted

    I thought I can do it with case statement. I tried doing this:

     select * from testTable
        order by
    	   case TestName
    	      when 'Prod' then 1
    		  when 'Share' then 2
    		  when 'Sports' then 3
    		  when 'Transportation' then 4
    		  when 'office' then 5
    		end
    

    the above did not work though. I also tried it the below way:

     select TestName from testTable
        order by
    	   case 
    	      when TestName= 'Prod' then '1'
    		  when TestName ='Share' then '2'
    		  when TestName= 'Sports' then '3'
    		  when TestName= 'Transportaion' then '4'
    		  when TestName= 'office' then '5'
    		end
    

    Friday, November 4, 2016 8:48 PM
  • User2053451246 posted

    Works fine with that code on my end.

    WITH cte AS (
    	SELECT 'Prod' AS Col1
    	UNION ALL
    	SELECT 'Share' AS Col1
    	UNION ALL
    	SELECT 'Sports' AS Col1
    	UNION ALL
    	SELECT 'Transportation' AS Col1
    	UNION ALL
    	SELECT 'Office' AS Col1
    )
    
    SELECT *
    FROM cte AS t
    ORDER BY
    	CASE Col1
    		WHEN 'Prod' THEN 1
    		WHEN 'Share' THEN 2
    		WHEN 'Sports' THEN 3
    		WHEN 'Transportation' THEN 4
    		WHEN 'office' THEN 5
    	END
    

    Output:

    Prod
    Share
    Sports
    Transportation
    Office

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2016 8:59 PM
  • User77042963 posted

    It should work:

    select Col1 from Tabl1
     order by
    	   case Col1
    	      when 'Prod' then 1
    		  when 'Share' then 2
    		  when 'Sports' then 3
    		  when 'Transportation' then 4
    		  when 'office' then 5
    		end
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2016 9:08 PM
  • User364663285 posted

    Hi All,

    I only have four categories in my table. I don't have any other data in my table:

    Tabl1
    
    Col1
    
    Office
    Transportation
    Prod
    Share
    Sports
    
    

    I need to sort them in this order

    Prod
    Share
    Sports
    Transportation
    Office


    How can I achieve the above sorting. I tried doing this, but the below did not work:

     select * from testTable
        order by
    	   case TestName
    	      when 'Prod' then 1
    		  when 'Share' then 2
    		  when 'Sports' then 3
    		  when 'Transportation' then 4
    		  when 'office' then 5
    		end
    

    Hi,

    Dynamic SQL can be your option for achieving this.

    Monday, November 7, 2016 6:06 AM