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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 23, 2015 10:34 AM
Points: 27, Visits: 123
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: Tuesday, May 26, 2015 1:03 AM
Points: 286, Visits: 333
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: Tuesday, July 7, 2015 2:25 PM
Points: 1,577, Visits: 2,451
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 @ 3:24 PM
Points: 37,999, Visits: 34,909
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 2,137, Visits: 22,616
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 @ 3:24 PM
Points: 37,999, Visits: 34,909
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 Sunday, September 2, 2012 9:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 2,137, Visits: 22,616
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 @ 3:24 PM
Points: 37,999, Visits: 34,909
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: Yesterday @ 5:36 PM
Points: 3,964, Visits: 6,324
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1353297
Posted Tuesday, September 4, 2012 12:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1354101
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse