Force result order

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  October 22, 2012 at 8:41 am

  #268988

  Hi geniuses,

  SQL:

  SELECT PROJ, CARAC.

  FROM X

  My results:

  PROJ|:|CARAC.

  a|:|Low

  b|:|None

  c|:|Low

  I want 'None' to appear first in my result.

  Solutions?

  Thanks

  Regards!

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 22, 2012 at 8:53 am

  #1551411

  Add an order by clause, something like:

  ORDER BY CASE CARAC WHEN 'None' THEN 1 ELSE 2 END ASC, PROJ ASC

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 22, 2012 at 9:17 am

  #1551434

  Here is one way;

  -- Created only to show effect of ORDER BY clause

  CREATE TABLE #t(proj VARCHAR(1),carac VARCHAR(5))

  INSERT INTO #t

  SELECT 'a','Low' UNION ALL

  SELECT 'b','None' UNION ALL

  SELECT 'c','Low'

  SELECT PROJ, CARAC FROM #t ORDER BY carac DESC

  Results:

  PROJCARAC

  bNone

  cLow

  aLow

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• davidandrews13

  SSCertifiable

  Points: 6809

  More actions

  October 22, 2012 at 9:19 am

  #1551437

  DECLARE @table TABLE(id varchar(10),descript varchar(10))

  INSERT INTO @table ([id], [descript])

  SELECT 'a','low'

  UNION

  SELECT 'b','none'

  UNION

  SELECT 'c','low'

  UNION

  SELECT 'd','qwerty'

  UNION

  SELECT 'e','abba'

  SELECT * FROM @table ORDER BY CASE descript WHEN 'none' THEN 0 ELSE 1 end,[descript]

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  October 22, 2012 at 9:37 am

  #1551465

  Thanks

  Im using:

  SELECT PROJ, CARAC

  FROM X

  ORDER BY CASE WHEN MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT = 'None' THEN MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT END DESC

  But it gives me repetitive results. Like 2 CARAC for PROJ.

  How do I solve this?

  Thanks

  Regards!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 22, 2012 at 9:44 am

  #1551469

  davdam8 (10/22/2012)

  ---

  Thanks

  Im using:

  SELECT PROJ, CARAC

  FROM X

  ORDER BY CASE WHEN MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT = 'None' THEN MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT END DESC

  But it gives me repetitive results. Like 2 CARAC for PROJ.

  How do I solve this?

  Thanks

  Regards!

  Your order by does not have a condition for when the value is not 'None'. What is wrong with the version Gazareth posted? That seems to be the simplest by far.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  October 22, 2012 at 9:53 am

  #1551481

  Yes, I omitted the rest.

  I got this:

  ...

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  The problem is for each PROJ I get 2 times the same CARAC.

  Before applying this order by case, I had DISTINCT, all good back then!

  But with the order by case clause, it is not possible to apply DISTINCT.

  I'm a rookie in SQL.

  Thanks

  Regards!

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 22, 2012 at 9:57 am

  #1551487

  davdam8 (10/22/2012)

  ---

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  That order by clause is functionally identical to "order by carac desc" 😀

  If you could post your full query we can help with the distinct problem - didn't know you were using distinct when I first answered.

  Thanks

  Gaz

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 22, 2012 at 9:59 am

  #1551489

  davdam8 (10/22/2012)

  ---

  Yes, I omitted the rest.

  I got this:

  ...

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  The problem is for each PROJ I get 2 times the same CARAC.

  Before applying this order by case, I had DISTINCT, all good back then!

  But with the order by case clause, it is not possible to apply DISTINCT.

  I'm a rookie in SQL.

  Thanks

  Regards!

  Look closely at your case expression. If CARAC = 'None' then CARAC else CARAC. That is the same thing as Case 1 when 1 then 1 else 1. All of your conditions are the same.

  Again...what is wrong with the version Gazareth posted? Here it is using the sample data that BitBucket posted.

  CREATE TABLE #t(proj VARCHAR(1),carac VARCHAR(5))

  INSERT INTO #t

  SELECT 'a','Low' UNION ALL

  SELECT 'b','None' UNION ALL

  SELECT 'c','Low'

  SELECT PROJ, CARAC FROM #t

  ORDER BY CASE CARAC WHEN 'None' THEN 1 ELSE 2 END ASC, PROJ ASC

  And I can't even comprehend what you are saying here:

  Before applying this order by case, I had DISTINCT, all good back then!

  But with the order by case clause, it is not possible to apply DISTINCT.

  The two are completely unrelated. You can use distinct and order by in the same query along with a case expression.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  October 22, 2012 at 10:01 am

  #1551493

  Here it goes

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  This is what I got at the moment.

  Thanks

  Regards!

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  October 22, 2012 at 10:10 am

  #1551500

  davdam8 (10/22/2012)

  ---

  Here it goes

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  This is what I got at the moment.

  Thanks

  Regards!

  Can you that is a full analogue of:

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CARAC DESC

  If you want a raw with 'None' in it to be returned as first in the list then try this:

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CASE WHEN CARAC='None' THEN 1 ELSE 2 END, CARAC DESC

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 22, 2012 at 10:11 am

  #1551502

  davdam8 (10/22/2012)

  ---

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  That's identical to:

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CARAC DESC

  Works for you in this situation, as 'None' comes before 'Low' when ordered alphabetically descending.

  But if there's other values in CARAC does it still hold true? Also note your PROJ column will be totally unordered.

  If you're using distinct with order by, your order by statements must also be in the select list.

• cphite

  SSCarpal Tunnel

  Points: 4278

  More actions

  October 22, 2012 at 10:16 am

  #1551507

  davdam8 (10/22/2012)

  ---

  Here it goes

  SELECT PROJ, CARAC

  FROM X

  WHERE CARAC<>''

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  This is what I got at the moment.

  Thanks

  Regards!

  Your case statement still isn't doing anything.

  There is no logical difference between this:

  ORDER BY CASE WHEN CARAC= 'None' THEN CARAC ELSE CARAC END DESC

  And this:

  ORDER BY CARAC DESC

  Think about it. When CARAC = 'None' you are returning the value of CARAC. When CARAC is not equal to 'None' you are... also returning the value of CARAC.

  What you need to do is something like this (which a few people have already given you):

  ORDER BY CASE WHEN CARAC= 'None' THEN 0 ELSE 1 END DESC, CARAC

  Look carefully.

  When the value of CARAC is 'None' the CASE is returning a 0... when it is not equal to 'None' the CASE is returning a 1. Then we are ordering by the CASE statement and by the value of CARAC.

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  October 22, 2012 at 10:18 am

  #1551510

  YES! Its the same, but besides None, Low, I also got Strong and Extreme.

  And basically what I want is for each PROJ, to appeaar by this order:

  None;Low;Strong and Extreme.

  Thanks

  Regards

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 22, 2012 at 10:19 am

  #1551513

  Why not normalize the CARAC values to a different table , add a 'SortOrder' Column and Bobs you uncle ?

  ---

  
  Clear Sky SQL
  My Blog[/url]