sql order by name, numbers at the end

Question

maybe it is a very childish question. can I order the record in that way that the values which begins with a number go to the end of the record? for example these are the values in the database:

1
Bill
Andrew

to turn in this with a query:

Andrew
Bill
1

Answer 1

No, numbers will go to the end only if you will select DESC ordering - when letters go as "z..a". You probably should perform two queries - by letters and by numbers - and union results.

Answer 2

ORDER BY -ISNUMERIC(a), a

Answer 3

A solution could to use two columns on your order by clause -- the first one not corresponding to a real DB columns, but based on some calculation :

• First, order by something that gives 1 for strings, and 2 for numbers
  • This will push all string to the beginning of the resultset
• And, then, order by the content of the column

Something like this (mostly pseudo-code) might help :

select *
from your_table
where ...
order by case when your_column >= '0' and your_column <= '9' then 2 else 1 end,
    your_column

The first part of the order by will ensure numbers are placed after non-numbers ; and the second part will just do a normal sort.

Note : I suppose the condition could be modified to use a function that determines if your_column contains a string or a number ; maybe there is some kind of is_number function, provided by your database system ?