Sort Order Headache

[DennisTheMenace]

My Sql Statment "Select * from Models Order by KeyNumber" shows:

KEY Item No. Description
=== ======== =================
1 123778 Product One
10 198812 Product Ten
2 234452 Product Two
21 123409 Product Twenty One
22 234569 Product Twenty Two
22A 123425A Product Twenty Two 'A'
22-B 123528 Product Twenty Two 'B'
3 078725 Product Three

Since the value in KeyNumber is NOT ALWAYS a number, the field is set to text - hence the results in my sort. Any suggestions in getting the double digits to sort properly - WITHOUT having to re-enter the Key Numbers as there are thousands on file??? (sorry if this has been addressed before, but the search feature here at Tek-Tips is not working at this time?!)

THANKS IN ADVANCE! =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[mwolf00]

Argh! That's a tough one! In the future, I'd recommend a dual key (numeric column and text column). Any chance that you can write a stored procedure to split up KEY into two new columns? We can help with the syntax. Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[DennisTheMenace]

No chance of splitting it up. There are 478,000+ records - that populate this field nightly from an existing legacy system. There are also other programs that make calls to this table that need it the way it is... SORRY! =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[tlbroadbent]

The following will handle the sorting of the sample you posted. You will need to expand and modify if three digit codes exist or other factors cause this to be an inadequate solution. Hopefully, this gives you an idea of what can be done using case statements.

Select *
From Models
Order by
Case When Len(KeyCode)=1
Then '0' + keycode
Else keycode
End Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[DennisTheMenace]

Thanks Terry!

The LEN goes up to 12... would you be able to provide an example of more than one, and I'll expand it out.

Also, is there a way to also then sort by description, as there are sometimes key numbers that are repeated.

"Select * from Models Order By keynumber, Description" =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[SQLBill]

Using Terry's suggestion:

Select *
From Models
Order by
Case When Len(KeyCode)=11
Then '0' + keycode
Case When Len(KeyCode)=10
Then '00' + keycode
Case When Len(KeyCode)=9
Then '000' + keycode
<etc.....>
Else keycode
End

Also, your order by will work like you want it to.

-SQLBill

 

[DennisTheMenace]

Thanks! I'll go give it a shot! =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[DennisTheMenace]

I Typed Terry's code in a StoreProcedure and syntax checked.
Sorry SQLBill... typed your code in and it returned:
Incorrect syntax near keyword CASE.

Also, how do I sort by description as well... in that same stored procedure?
Select *
From Models
Order by
Case When Len(KeyCode)=1
Then '0' + keycode, description
Else keycode, description
End =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[tlbroadbent]

Put the description after the END. That indeicates the END of the CASE function. SQLBill has unneeded CASE keywords. Remove them as in the following.

Select *
From Models
Order by
Case
When Len(KeyCode)=11 Then '0' + keycode
When Len(KeyCode)=10 Then '00' + keycode
When Len(KeyCode)=9 Then '000' + keycode
<etc.....>
Else keycode
End, description Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[alfredp77]

I think we can make it simpler:

Select *
From Models
Order by replicate('0',12-len(KeyCode)) + KeyCode, description

 

[topher0303]

If I am understanding the question correctly, you want to sort on the numeric portion of the key column only (ignoring alpha characters). If so, you will probably have to do something like this:

SELECT *
from models
order by convert(bigint,CASE WHEN IsNumeric(SubString(key, 1, 12)) = 1
THEN key
WHEN IsNumeric(SubString(key, 1, 11)) = 1
THEN SubString(key, 1, 11)
WHEN IsNumeric(SubString(key, 1, 10)) = 1
THEN SubString(key, 1, 10)
WHEN IsNumeric(SubString(key, 1, 9)) = 1
THEN SubString(key, 1, 9)
WHEN IsNumeric(SubString(key, 1, 8)) = 1
THEN SubString(key, 1, 8)
WHEN IsNumeric(SubString(key, 1, 7)) = 1
THEN SubString(key, 1, 7)
WHEN IsNumeric(SubString(key, 1, 6)) = 1
THEN SubString(key, 1, 6)
WHEN IsNumeric(SubString(key, 1, 5)) = 1
THEN SubString(key, 1, 5)
WHEN IsNumeric(SubString(key, 1, 4)) = 1
THEN SubString(key, 1, 4)
WHEN IsNumeric(SubString(key, 1, 3)) = 1
THEN SubString(key, 1, 3)
WHEN IsNumeric(SubString(key, 1, 2)) = 1
THEN SubString(key, 1, 2)
WHEN IsNumeric(SubString(key, 1, 1)) = 1
THEN SubString(key, 1, 1)
ELSE NULL
END)


Unfortunately, this will probably slow the response down a bit.

Chris.

 

[SQLBill]

Thanks to Terry for correcting my error...it was too long of a day I guess.

-SQLBill

 

[DennisTheMenace]

Thanks for everyone's input!

alfredp77 - your solution WAS simpler... however, like SQLBill & Terry's solutions, the key numbers such as 46A, and 49C were not properly sorted. They showed up at the bottom of the list.

The final results look liked this (THANKS TOPHER!!) and it works just great. Takes into consideration 12-A, 12-4, 12.3, 12B, etc... Note: See bold text at bottom. I had to add the &quot;, KeyNumber&quot; to make sure 12, 12A, 12B, 12-C sorted corectly.

THANKS AGAIN EVERYONE! breathes sigh of relief

'====Start Stored Procedure====================
CREATE Procedure sp_RetrieveModProd
@chrModel varchar(255)
AS
select * from ModProds
where chrModel = @chrModel
order by convert(bigint,CASE
WHEN IsNumeric(SubString(KeyNumber, 1, 12)) = 1
THEN KeyNumber
WHEN IsNumeric(SubString(KeyNumber, 1, 11)) = 1
THEN SubString(KeyNumber, 1, 11)
WHEN IsNumeric(SubString(KeyNumber, 1, 10)) = 1
THEN SubString(KeyNumber, 1, 10)
...
WHEN IsNumeric(SubString(KeyNumber, 1, 2)) = 1
THEN SubString(KeyNumber, 1, 2)
WHEN IsNumeric(SubString(KeyNumber, 1, 1)) = 1
THEN SubString(KeyNumber, 1, 1)
ELSE NULL
END), KeyNumber, chrDescription
GO
'=====End Stored Procedure==================== =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)