custom sorting in sql 1

[jared5494]

Hello,
Ok here is the problem:

i have a column named col1 that contains either 'a', 'c', 'd', 'k', 'n'.

i want to sort in that order, but when i do this sql command, it obviously sorts them alphabetically

select othercolumn, col1
from tablx
order by col1

what is the syntax to do a custom sort. does that make any sense?

thanks in advance,
Jared Weinfurtner

 

[gmmastros]

Here's one way...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](1))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'c'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'d'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'k'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'n'[/color])

[COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Order[/color] [COLOR=blue]BY[/color] [COLOR=blue]Case[/color] Data [COLOR=blue]When[/color] [COLOR=red]'c'[/color] [COLOR=blue]Then[/color] 1
                   [COLOR=blue]When[/color] [COLOR=red]'d'[/color] [COLOR=blue]Then[/color] 2
                   [COLOR=blue]When[/color] [COLOR=red]'k'[/color] [COLOR=blue]Then[/color] 3
                   [COLOR=blue]Else[/color] 4
                   [COLOR=blue]End[/color]

-George

"the screen with the little boxes in the window." - Moron

 

[jared5494]

worked like a charm! Thank you VERY MUCH! i was looking into cases, but couldnt figure out how to apply it like that. again THANKS!

-Jared Weinfurtner

 

[gmmastros]

Here's one way...

Do you want to know what the other way is?



-George

"the screen with the little boxes in the window." - Moron

 

[r937]

i have a column named col1 that contains either 'a', 'c', 'd', 'k', 'n'.

i want to sort in that order, but when i do this sql command, it obviously sorts them alphabetically

may i ask what the problem is?


a,c,d,k,n is sorted in that order alphabetically



r937.com | rudy.ca