Conversion failure in custom sort

• Chrissy321

  SSCoach

  Points: 15763

  More actions

  January 14, 2011 at 12:55 pm

  #232959

  Hello All,

  Please help if you can.

  CREATE TABLE #Test

  (Type varchar(4),Sort smallint)

  INSERT INTO #Test

  SELECT 'a',NULL UNION

  SELECT 'b',NULL UNION

  SELECT 'c',NULL UNION

  SELECT 'yr1',1 UNION

  SELECT 'yr2',2 UNION

  SELECT 'yr10',10

  I want to ORDER BY the sort coumn if it exists and the type column if sort is NULL.

  You see this will give a conversion error.

  SELECT

  *

  FROM #Test

  ORDER BY COALESCE(Sort,Type)

  DROP TABLE #Test

  If I convert ORDER BY COALESCE(CONVERT(varchar(4),Sort),Type) I don't get the desired results.

  Any thoughts??

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  January 15, 2011 at 11:45 am

  #1273929

  Doesn't the following give you want you want?

  ORDER BY Sort, Type

  If not, something similar should give you the desired results.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 15, 2011 at 12:37 pm

  #1273933

  Chrissy321 (1/14/2011)

  ---

  Hello All,

  ...

  I want to ORDER BY the sort coumn if it exists and the type column if sort is NULL.

  That makes very little sense in terms of a set of rows - one is integer, one is a string - they sort differently...

  you would end up with a set of data like this:

  1

  2

  10

  a

  b

  c

  - What is the correct way to sort that output? Only by converting it to a common datatype can any sort make sense.

  I presume the problem you have would be that it comes out like this:

  1

  10

  2

  a

  b

  c

  yes?

  Please show how you want it sorted...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]