Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ORDER BY PROBLEM Expand / Collapse
Author
Message
Posted Saturday, September 1, 2012 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 3:46 AM
Points: 18, Visits: 100
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

Post #1353126
Posted Saturday, September 1, 2012 4:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:32 AM
Points: 277, Visits: 290
You need to select query as
select * from @NumberTable
order by Convert(numeric,REPLACE(REPLACE(ItemNumber,'ABC-',''),'-xyz','')) asc


Thanks.......
-----------------------------------
My Blog | Articles
Post #1353136
Posted Saturday, September 1, 2012 6:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #1353141
Posted Saturday, September 1, 2012 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353143
Posted Saturday, September 1, 2012 9:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 1,932, Visits: 19,917
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
Post #1353145
Posted Saturday, September 1, 2012 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353146
Posted Saturday, September 1, 2012 1:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
>> 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;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1353163
Posted Sunday, September 2, 2012 9:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 1,932, Visits: 19,917
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
Post #1353230
Posted Sunday, September 2, 2012 10:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353241
Posted Sunday, September 2, 2012 7:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1353297
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse