sorting in certain order

• anjaliagarwal5

  Ten Centuries

  Points: 1267

  More actions

  November 4, 2016 at 12:38 pm

  #315217

  Hi All,

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

  [Code]

  Tabl1

  Col1

  Office

  Transportation

  Prod

  Share

  Sports

  [/code]

  I need to sort them in the below order

  Prod

  Share

  Sports

  Transportation

  Office

  How can I achieve the above sorting.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 4, 2016 at 12:41 pm

  #1910707

  Use a combination of ORDER BY and CASE.

  ---

• anjaliagarwal5

  Ten Centuries

  Points: 1267

  More actions

  November 4, 2016 at 2:46 pm

  #1910732

  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

  it doesn't sort the way, I want.

• sgmunson

  SSC Guru

  Points: 110644

  More actions

  November 4, 2016 at 2:54 pm

  #1910734

  anjaliagarwal5 (11/4/2016)

  ---

  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

  it doesn't sort the way, I want.

  If that's not working, try it this way instead:

  SELECT *,

  CASE TestName

  WHEN 'Prod' THEN 1

  WHEN 'Share' THEN 2

  WHEN 'Sports' THEN 3

  WHEN 'Transportation' THEN 4

  WHEN 'Office' THEN 5

  END AS SORT_FIELD

  FROM testTable

  ORDER BY SORT_FIELD;

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• ryanbesko

  Default port

  Points: 1424

  More actions

  November 4, 2016 at 2:58 pm

  #1910735

  What you tried should be working. Worked for me.

  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 t.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

  This matches exactly what you asked for.

• prabhu.st

  SSCrazy

  Points: 2583

  More actions

  November 8, 2016 at 12:40 am

  #1911113

  Hi Anjali,

  whatever you tried (that is what suggested by phil as well) is the shortest way to achieve this requirement.

  Thanks,

  Prabhu