ORDER BY PROBLEM

• santa326

  SSC Eights!

  Points: 925

  More actions

  September 1, 2012 at 1:18 am

  #280676

  Hi,

  I have column in a table which needs to be ordered in an ascending order.

  DECLARE@NumberTable TABLE( ItemNumber VARCHAR(50))

  INSERT@NumberTable

  SELECT'ABC-1702-XYZ' ItemNumber UNION ALL

  SELECT'ABC-1727-XYZ'UNION ALL

  SELECT'ABC-1729-XYZ'UNION ALL

  SELECT'ABC-895-XYZ'UNION ALL

  SELECT'ABC-2119-XYZ'UNION ALL

  SELECT'ABC-1746-XYZ'UNION ALL

  SELECT'ABC-900-XYZ'UNION ALL

  SELECT'ABC-2215-XYZ'UNION ALL

  SELECT'ABC-645-XYZ'UNION ALL

  SELECT'ABC-1783-XYZ'UNION ALL

  SELECT'ABC-2193-XYZ'UNION ALL

  SELECT'ABC-830-XYZ'UNION ALL

  SELECT'ABC-646-XYZ'UNION ALL

  SELECT'ABC-796-XYZ'

  select * from @NumberTable

  order by ItemNumber ASC

  But the result is not as intended.

  I want the result to be order according to the number only.In all cases "ABC" will be present but "xyz" may change in character length. there are 2 "-" characters in every row.

  How do i do this.

  Regards

  Ravi T

• UmaShankar Patel

  Ten Centuries

  Points: 1142

  More actions

  September 1, 2012 at 4:03 am

  #1532674

  You need to select query as

  select * from @NumberTable

  order by Convert(numeric,REPLACE(REPLACE(ItemNumber,'ABC-',''),'-xyz','')) asc

  Thanks.......
  -----------------------------------
  My Blog[/url] | Articles

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  September 1, 2012 at 6:41 am

  #1532679

  UmaShankar Patel (9/1/2012)

  ---

  You need to select query as

  select * from @NumberTable

  order by Convert(numeric,REPLACE(REPLACE(ItemNumber,'ABC-',''),'-xyz','')) asc

  The problem I see with this is that 'ABC' may not always be 'ABC', and 'XYZ' may not always be 'XYZ'. This should do it, albeit maybe a little bulky.

  SELECT

  ItemNumber

  FROM @NumberTable

  ORDER BY CAST(SUBSTRING(SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1) + 1,LEN(ItemNumber)),1,CHARINDEX('-',SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1)+1,LEN(ItemNumber)),1)-1) AS INT)

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  September 1, 2012 at 8:31 am

  #1532681

  UmaShankar Patel (9/1/2012)

  ---

  You need to select query as

  select * from @NumberTable

  order by Convert(numeric,REPLACE(REPLACE(ItemNumber,'ABC-',''),'-xyz','')) asc

  Like Ravi said, the "XYZ" portion could change so this won't work.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 1, 2012 at 9:23 am

  #1532683

  a variation....

  DECLARE@NumberTable TABLE( ItemNumber VARCHAR(50))

  INSERT@NumberTable

  SELECT'ABC-1702-XYZ' ItemNumber UNION ALL

  SELECT'ABC-1727-XYZ'UNION ALL

  SELECT'ABC-1729-XYZ'UNION ALL

  SELECT'ABC-895-XYZ'UNION ALL

  SELECT'ABC-2119-XYZ'UNION ALL

  SELECT'ABC-1746-XYZqqqq'UNION ALL

  SELECT'ABC-900-dfdfdfXYZ'UNION ALL

  SELECT'ABCasasa-2215-XYZabc'UNION ALL

  SELECT'ABC-64533333-XYZ'UNION ALL

  SELECT'ABC121-1783-333XYZ'UNION ALL

  SELECT'ABC-2193-XYZ'UNION ALL

  SELECT'ABC-830-XYZ'UNION ALL

  SELECT'ABC-646-XYZ'UNION ALL

  SELECT'ABC-796-XYZ'

  select *

  from @NumberTable

  order by cast (SUBSTRING(

  ItemNumber,

  CHARINDEX('-', ItemNumber) + 1,

  LEN(ItemNumber) - CHARINDEX('-', ItemNumber) - CHARINDEX('-', REVERSE(ItemNumber))) as int)

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  September 1, 2012 at 10:00 am

  #1532684

  Gregs code works a treat on this but, let me ask, how often will you need to do such a sort, will you ever need to limit the return based on the calculation (for example, return only the numbers from 1000 to 2000), and is performance really important (read that as how many rows you have and how fast you need it to return)?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 2, 2012 at 9:22 am

  #1532735

  CELKO (9/1/2012)

  ---

  >> I have column in a table which needs to be ordered in an ascending order. <<

  Somebody did not do his homework! Tables have no ordering BY DEFINITION. If you want to have such a relationship in the table, you need to add a column for it; this is called “The Information Principle” and it is one of Dr. Codd's 12 rules. If you want to display data in a sorted order outside the database, then use the ORDER BY clause to create an implicit cursor. Is that what you meant?

  When you have read that book on RDBMS, then read a book on SQL, so your will know the ANSI Standard syntax for insertion.

  INSERT INTO Items

  VALUES ('ABC-1702-XYZ'), ('ABC-1727-XYZ'),

  ('ABC-1729-XYZ'), ('ABC-895-XYZ'), ('ABC-2119-XYZ'),

  ('ABC-1746-XYZ'), ('ABC-900-XYZ'), ('ABC-2215-XYZ'),

  ('ABC-645-XYZ'), ('ABC-1783-XYZ'), ('ABC-2193-XYZ'),

  ('ABC-830-XYZ'), ('ABC-646-XYZ'), ('ABC-796-XYZ');

  One answer is this. It is long but fast.

  SELECT item_code

  FROM (SELECT item_code,

  CAST (REPLACE (

  REPLACE (REPLACE (REPLACE (

  REPLACE (REPLACE (REPLACE (item_code, 'A', ''),

  'B', ''),

  'C', ''),

  'X', ''),

  'Y', ''),

  'Z', ''),

  '-', '') AS INTEGER) AS sort_numeric_value

  FROM Items)

  ORDER BY sort_numeric_value;

  as per OP's original request

  "In all cases "ABC" will be present but "xyz" may change in character length. there are 2 "-" characters in every row."

  what happens when we have rows that contain 'ABC-123-xyzpqr' ?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  September 2, 2012 at 10:12 am

  #1532745

  CELKO (9/1/2012)

  ---

  Somebody did not do his homework! Tables have no ordering BY DEFINITION.

  Relax... The OP actually knows that. That's why he asked for help in producing an ordered output.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  September 2, 2012 at 7:03 pm

  #1532787

  I don't know if this is any lighter or better performing but it's another option:

  SELECT *

  FROM @NumberTable

  ORDER BY RIGHT('000' +

  REPLACE(

  SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''), 4)

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  September 4, 2012 at 12:06 pm

  #1533446

  dwain.c (9/2/2012)

  ---

  I don't know if this is any lighter or better performing but it's another option:

  SELECT *

  FROM @NumberTable

  ORDER BY RIGHT('000' +

  REPLACE(

  SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''), 4)

  Expanding on the above idea (good one) but to sort all 3 parts separately try this. It will sort the inside numeric values the same, but will also sort the first alphanumeric part which gives a slightly different order. I included the "parts" in the query just to show how the string is being disassembled. You could change the ordering to include just the 2nd part if you want or make it first or whatever. So the sub-query is not really necessary if you don't mind returning the RowNum value (and in which case you don't even need an ORDER BY clause).

  SELECT

  Result.ItemNumber

  ,Result.Part1

  ,Result.Part2

  ,Result.Part3

  FROM

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY

  REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-','')

  ,CAST(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', '') AS INT)

  ,REPLACE(REPLACE(REPLACE(ItemNumber,REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-',''),''),REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),''),'-','')

  ) AS RowNum

  ,ItemNumber

  ,REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-','') AS Part1

  ,REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', '') AS Part2

  ,REPLACE(REPLACE(REPLACE(ItemNumber,REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-',''),''),REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),''),'-','') AS Part3

  FROM @NumberTable

  ) AS Result

  ORDER BY

  Result.RowNum

  SELECT

  ROW_NUMBER() OVER (ORDER BY

  REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-','')

  ,CAST(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', '') AS INT)

  ,REPLACE(REPLACE(REPLACE(ItemNumber,REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-',''),''),REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),''),'-','')

  ) AS RowNum

  ,ItemNumber

  FROM @NumberTable

  Then, if you want to expand on this even a bit more, you could group on say the second numeric part and ignore the 3rd part by adding a PARTITION BY clause and using the RowNum as a filter. This example will return just one of the '2119' values which might be desirable if these were some parent set such as an item category. You can also nest the ROW_NUMBER() statement to create nested levels that the date can be grouped upon, but I'll leave that to others for experimentation.

  DECLARE@NumberTable TABLE( ItemNumber VARCHAR(50))

  INSERT@NumberTable

  SELECT'ABC-1702-XYZ' ItemNumber UNION ALL

  SELECT'ABC-1727-XYZ'UNION ALL

  SELECT'ABC-1729-XYZ'UNION ALL

  SELECT'ABC-895-XYZ'UNION ALL

  SELECT'ABC-2119-XYZ'UNION ALL

  SELECT'ABC-2119-XYZqqqq'UNION ALL

  SELECT'ABC-2119-dfdfdfXYZ'UNION ALL

  SELECT'ABCasasa-2215-XYZabc'UNION ALL

  SELECT'ABC-64533333-XYZ'UNION ALL

  SELECT'ABC121-1783-333XYZ'UNION ALL

  SELECT'ABC-2193-XYZ'UNION ALL

  SELECT'ABC-830-XYZ'UNION ALL

  SELECT'ABC-646-XYZ'UNION ALL

  SELECT'ABC-796-XYZ'

  SELECT

  ItemNumber

  FROM

  (

  SELECT

  ROW_NUMBER() OVER (

  PARTITION BY

  CAST(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', '') AS INT)

  ORDER BY

  REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-','')

  ,CAST(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', '') AS INT)

  ,REPLACE(REPLACE(REPLACE(ItemNumber,REPLACE(SUBSTRING(ItemNumber,1,CHARINDEX(REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),ItemNumber)-1),'-',''),''),REPLACE(SUBSTRING(ItemNumber, PATINDEX('%[0-9]%', ItemNumber), 4), '-', ''),''),'-','')

  ) AS RowNum

  ,ItemNumber

  FROM @NumberTable

  ) AS Result

  WHERE

  RowNum = 1

• SWATH

  Old Hand

  Points: 303

  More actions

  September 5, 2012 at 6:42 am

  #1533767

  select * from @NumberTable

  ORDER BY CAST(REPLACE(SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber)+1,

  CHARINDEX('-',REVERSE(ItemNumber))),'-',' ') AS INT)

• SWATH

  Old Hand

  Points: 303

  More actions

  September 5, 2012 at 6:47 am

  #1533769

  santa326 (9/1/2012)

  ---

  Hi,

  I have column in a table which needs to be ordered in an ascending order.

  DECLARE@NumberTable TABLE( ItemNumber VARCHAR(50))

  INSERT@NumberTable

  SELECT'ABC-1702-XYZ' ItemNumber UNION ALL

  SELECT'ABC-1727-XYZ'UNION ALL

  SELECT'ABC-1729-XYZ'UNION ALL

  SELECT'ABC-895-XYZ'UNION ALL

  SELECT'ABC-2119-XYZ'UNION ALL

  SELECT'ABC-1746-XYZ'UNION ALL

  SELECT'ABC-900-XYZ'UNION ALL

  SELECT'ABC-2215-XYZ'UNION ALL

  SELECT'ABC-645-XYZ'UNION ALL

  SELECT'ABC-1783-XYZ'UNION ALL

  SELECT'ABC-2193-XYZ'UNION ALL

  SELECT'ABC-830-XYZ'UNION ALL

  SELECT'ABC-646-XYZ'UNION ALL

  SELECT'ABC-796-XYZ'

  select * from @NumberTable

  order by ItemNumber ASC

  But the result is not as intended.

  I want the result to be order according to the number only.In all cases "ABC" will be present but "xyz" may change in character length. there are 2 "-" characters in every row.

  AND MY ANSWER FOR THIS QUESTION IS

  select * from @NumberTable

  ORDER BY CAST(REPLACE(SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber)+1,CHARINDEX('-',REVERSE(ItemNumber))),'-',' ') AS INT)