views:

268

answers:

3

Hi folks,

i have a list of alphanumeric tokens, say '1a', '1b', '02', '03', '10', '11', etc...

Now, what's the best way to do an order by on this list of tokens?

I am getting '1a', '1b', '10', '11', '02', '03',

but i need it to be

'1a', '1b', '02', '03', '10', '11' 

UPDATE

ok, i am doing this after the suggestion but it's not working.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
select '1b'
select '02'
select '10'

select * from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

I am getting the response as '1b', '02', '10', '1a'

UPDATE2

It works after making the following change.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
insert into @tokens
select '1b'
insert into @tokens
select '02'
insert into @tokens
select '10'


select token from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

Thanks to all of you for your nice ideas.

+4  A: 

The easiest solution is to pre-pend zeros

Select ...
From Table
Order By Right( '0000000000' + YourColumn, 10)

However, this will not account for alpha characters. In order to deal with alpha characters, then you'd need to know how many potential alpha characters you might have. If there is one, you could do something like:

Select ...
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

ADDITION

Test run:

If object_id('tempdb..#Test') is not null
    Drop Table #Test

Create Table #Test ( NumVal varchar(10) )
Insert #Test(NumVal) Values('02')
Insert #Test(NumVal) Values('03')
Insert #Test(NumVal) Values('1a')
Insert #Test(NumVal) Values('1b')
Insert #Test(NumVal) Values('10')
Insert #Test(NumVal) Values('11')

Select NumVal
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

Results:
1a
1b
02
03
10
11

A note about my solution. If it is the case that the alphabetic character(s) has special meaning, then as Erick Robertson suggested, you should break up the data into separate columns. The above solution will only handle two very specific cases: an all numeric value, a value with a single trailing alphabetic character. If the data might have multiple alphabetic characters or the alphabetic character is sometimes positioned other than the end of the value, my solution will not work. In addition, it should be noted that my solution will cause a table scan to evaluate the order-able string on each value.

If what you seek is a one-time quick solution, then my approach will work. If you are seeking a long term solution, then either break up the data into separate columns, accept the goofy sort order or add a column that dictates the relative sort order for each value.

Thomas
Why wouldn't this put '0000000002' ahead of '000000001a'?
Erick Robertson
@Erick Robertson - Revised my post. Didn't get a chance to flesh it all out the first time.
Thomas
This certainly doesn't seem like the easiest solution anymore. :)
Erick Robertson
@Erick Robertson - Definitely not the fastest solution. The only other way would be to split the numeric characters from non-numeric which also wouldn't be fast.
Thomas
@Thomas - i tried your suggestion, it's not working, pls see my update.
SoftwareGeek
@SoftwareGeek - I've posted my test code. As far as I can tell, it's working.
Thomas
You're taking the hard route by going this way. Create a separate column, write a script to fill it, modify the application to maintain it, and order by that column first. It will work.
Erick Robertson
@SoftwareGeek, @Erick Robertson - I agree, if changing the schema is an option. It should also be noted that my solution will only handle two very specific cases: fully numeric or a *single* alphabetic character at the end. If you have values like '1a10', '1aa' or 'a99' it won't work.
Thomas
@Thomas - the values have a simple logic. On a repeat of the sequence it adds an alphanumeric character, so the data points you mentioned won't occur.
SoftwareGeek
@Thomas - Ah, problem solved. My sample was not inserting all the data into the table. Corrected but i am curious what makes the prepending of '0' sort the data correctly?
SoftwareGeek
@Thomas - Is it possible to specify sort order 'ASC' or 'DESC'? How to do it in reverse order?
SoftwareGeek
@SoftwareGeek - RE: ASC/DESC, Yes you can simply append "DESC" to the Order By clause to sort descending. The reason for pre-pending zeroes is that you are sorting text, not numbers. Thus, the values 1, 10, 2 sort in that order because the character "1" sorts before "2". Adding zeros converts those same sample values into something like 001, 010, 002 and thus they sort correctly because the character "0" sorts before the character "1".
Thomas
+1  A: 

The best solution is to have a separate field which stores the int value of the token. You should maintain this column when you maintain the token column. Then when you sort, order by the int value column then the token column. This will allow you to index these columns for fast retrieval of data with large data sets.

Conversion functions from alpha to int are slow and cannot take advantage of indexing to speed up queries. As your data set grows, this type of solution will only get slower and bog down your database.

Erick Robertson
@Erick Robertson - respectfully, i have to disagree. There's got to be a better way. I like the solution here, it works. Alternately, i would have also have gone with @Rob's suggestion.
SoftwareGeek
@SoftwareGeek - The best way to do it is a way that you can take advantage of database indices. Of course, if you don't have access to alter the database, then the best solution is out of the question. The designer of the database should have realized that people would want to get the data out in this method and designed accordingly.
Erick Robertson
+2  A: 

If you're familiar with C# or VB.net, it might be worth considering writing a CLR function that performs the sorting for you as this sort order is non-standard enough to be quite hard to describe comprehensively and correctly in TSQL.

Rob
@Rob - yes, v.good suggestion but if db can perform the task then i let it handle it.
SoftwareGeek
@SoftwareGeek, I get what you're saying, but,... CLR functions are pretty much "as good" as TSQL ones performance wise and it's never a bad thing to have another tool available, particularly one that's better suited to a given task =)
Rob