If I have a list of 1,2,3,4,5,K and I want it ordered like K,1,2,3,4,5 how can I do this in SQL? Those values are the only ones in the SQL table. This is in MySQL, but other databases are also welcomed.
-
It's not clear whether these are rows in a table/resultset, or a single string of comma-separated values in which you want the order of the values changed.Hammerite– Hammerite2012-05-01 20:04:39 +00:00Commented May 1, 2012 at 20:04
-
What are your rules for sorting? Letters before numbers?Mark Byers– Mark Byers2012-05-01 20:04:46 +00:00Commented May 1, 2012 at 20:04
Add a comment
|
4 Answers
There's no integer dection function in mysql, so you'll have to do something like this. Assuming your column is varchar or the like, you could something simple like this
ORDER BY CASE WHEN field REGEXP '^-?[0-9]+$' THEN field ELSE '0'+field END
2 Comments
Mike Flynn
Cant I convert it to a signed int?
dfb
Sure, but when it fails, you'll have a null. This does it in one step
In Oracle, you could do:
ORDER BY CASE WHEN column='K' THEN 0 ELSE TO_NUMBER(column) END
Other systems presumably have similar constructs.
Comments
If you need to sort the SELECT's result, then you might just declare your custom sorting rules in ORDER BY statement.
Here's an example: http://www.emadibrahim.com/2007/05/25/custom-sort-order-in-a-sql-statement/
The idea is to assign your own priority to each item.
Bear in mind that it might be dependant on the actual SQL server.
Comments
Here's one that works in SQLite:
select v1.* from (
select t3.*, (case when value = 'K' then '0' else value end) normalized_val
from t3) v1
order by v1.normalized_val;
