Sorting a column 1

[mailtome]

Hi
I have a varchar column say Issue with data as follow

900
1100
update
test
800

Now I want to sort this column but the sort by query sorts it like

1100
800
900
test
update

Can anyone tell me the correct way to sot this column

 

[TonyGroves]

What way do you want it sorted?

 

[r937]

order 
    by case when Issue regexp '[0-9]' 
            then 0 else 1 end
     , 0+Issue
     , Issue

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TonyGroves]

Nice idea Rudy, but what happens with a value like "r937"? It matches the regex, so goes to the start of the list. [tt]REGEXP '^[0-9]'[/tt] might be an improvement, depending on what order the OP wants.

 

[r937]

great catch, tony

order 
    by Issue regexp '^[0-9]' desc 
     , 0+Issue 
     , Issue

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[r937]

oops, further testing shows it's still not quite right

try this --

order 
    by Issue regexp '[^0-9]' 
     , 0+Issue 
     , Issue

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[mailtome]

SELECT col FROM foo
ORDER BY col REGEXP "^[^0-9]", col+0, col

This query works while sorting in ascending order
While SELECT col FROM foo
ORDER BY col REGEXP "^[^0-9]", col+0, col DESC doesnt works

 

[r937]

ORDER BY col REGEXP "^[^0-9]" DESC , col+0 DESC , col DESC

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)