I have table with names as sth-1, sth-2, .........sth-10, sth-11 and so on.
I want to sort them serially but it fetches as 1, 11, 12, .., 2,3 and so on when sorted by name in asc order.
Any help appreciated
3 Answers
This should work for you if the numbers are always going to follow the -
SELECT *
FROM table
ORDER BY CAST(SUBSTRING(column,LOCATE('-',column)+1) AS SIGNED)
This original query is from
Sorting string column containing numbers in SQL?
Adopted to your needs
5 Comments
t.niese
Just want to note that this is only useful for small number of rows, because that way mysql could not use an index for sorting.
skv
Yes that is true... a better option is to store this number separately in a field and update it with a trigger
skv
Yes if that assumption is true then this can be done, the basic idea is that the sort should be on a number
user1609828
I had seen the same answer, but it fetches the even rows only, 2, 4, 6 and so on. The odd rows are missing.
skv
can you make a sqlfiddle, there is nothing here that filters the results so there could be some other part of the query that filters
This is because you're trying to sort strings, not numbers.
One way out of this is to make a separate int column so it will be a bit faster on the sorting.
The other way is this one:
SELECT * FROM `table` ORDER BY CAST(SUBSTRING(column,LOCATE('-',column)+1) AS SIGNED)
Personally I prefer to use a separate column, so you don't really rely on the names format.
Comments
An alternative way of doing this is to sort by the length and then the field:
order by char_length(col), col
natsort()if you want to do it in PHP