[dba-SQLServer] sorting problem

Billy,

Perhaps this code will give you some ideas to build on as I imagine
your problem is more complex than your example suggests:

select the_value
from #the_table
order by
    case 
        when ascii(right(the_value,1)) between 48 and 57
            then cast(substring(the_value,2,len(the_value) - 1) as int)
        else cast(substring(the_value,2,len(the_value) - 2) as int)
    end, the_value

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Billy Pang
> Sent: Friday, October 29, 2004 8:35 PM
> To: dba-SQLServer at databaseadvisors.com
> Subject: [dba-SQLServer] sorting problem
> 
> 
> Hello:
> 
> I have a table that contains one column with the following six values:
> 
> a1
> a2
> a21
> a10x
> a2a
> a10
> 
> Currently if that column is sorted, it is sorted this way:
> 
> a1
> a10
> a10x
> a2
> a21
> a2a
> 
> But I want it to be sorted this way:
> 
> a1
> a2
> a2a
> a10
> a10x
> a21
> 
> (note: a2a comes before a10, a21 goes after a10x, etc...ergo, 
> letters come 
> before numbers, contrary to their ascii values)
> 
> Is it possible or is it a pipe dream?  Below is copy of code.
> 
> Thanks in advance,
> Billy
> 
> /* cut here */
> use tempdb
> create table #the_table(the_value varchar(10));
> insert into #the_table(the_value) values('a1');
> insert into #the_table(the_value) values('a2');
> insert into #the_table(the_value) values('a21');
> insert into #the_table(the_value) values('a10x');
> insert into #the_table(the_value) values('a2a');
> insert into #the_table(the_value) values('a10');
> select * from #the_table order by the_value
> drop table #the_table;
> /* cut here */