开发者

sql order by name, numbers at the end

开发者 https://www.devze.com 2022-12-21 20:58 出处:网络
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?

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 t开发者_StackOverflow社区urn in this with a query:

Andrew
Bill
1


ORDER BY -ISNUMERIC(a), a


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.


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 ?

0

精彩评论

暂无评论...
验证码 换一张
取 消