Order by CASE

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  March 22, 2013 at 3:54 am

  #275531

  Hi geniuses, need some help here.

  Select r.Title, r.impact,r.probability

  FROM DB_Tabs_Risk r

  Order by r.impact desc

  Titleimpactprobability

  RiskA100,1

  RiskB100,5

  RiskC90,8

  RiskD50,5

  RisckE50,3

  I need to order this based on something like

  When r.impact >= 7 AND r.probability >= .7 (on top)

  When r.impact <= 3 AND r.probability <= .3 (bottom

  Else (middle)

  How do I T-SQL'it?

  Thanks in advnce.

• davidandrews13

  SSCertifiable

  Points: 6809

  More actions

  March 22, 2013 at 4:50 am

  #1599713

  WITH MyCTE(title,impact,probability) AS

  (

  SELECT 'RiskA',10,0.1 union

  select 'RiskB',10,0.5 union

  select 'RiskC',9,0.8 union

  select 'RiskD',5,0.5 union

  SELECT 'RisckE',5,0.3 UNION

  SELECT 'riskf',6,0.8 UNION

  SELECT 'riskg',2,0.3 UNION

  SELECT 'riskh',6,0.8

  )

  SELECT *,

  CASE WHEN [mc].[impact] >= 7 AND [mc].[probability] >= 0.7 THEN 0

  WHEN [mc].[impact] <= 3 AND [mc].[probability] >= 0.3 THEN 2

  ELSE

  1

  END AS SortCol

  FROM [MyCTE] [mc]

  ORDER BY (CASE WHEN [mc].[impact] >= 7 AND [mc].[probability] >= 0.7 THEN 0

  WHEN [mc].[impact] <= 3 AND [mc].[probability] >= 0.3 THEN 2

  ELSE

  1

  END )