Mysql order by, sorting doesn't work properly

Question

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

Note that you also have `natsort()` if you want to do it in PHP — Vlad Preda, Sep 08 '13 at 10:52

score 2 · Answer 1 · edited May 23 '17 at 11:50

2

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

Adopted to your needs

edited May 23 '17 at 11:50

Community

answered Sep 08 '13 at 10:49

skv

1

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. – t.niese Sep 08 '13 at 10:51
2

Yes that is true... a better option is to store this number separately in a field and update it with a trigger – skv Sep 08 '13 at 10:51
1

Yes if that assumption is true then this can be done, the basic idea is that the sort should be on a number – skv Sep 08 '13 at 10:54
I had seen the same answer, but it fetches the even rows only, 2, 4, 6 and so on. The odd rows are missing. – user1609828 Sep 08 '13 at 11:14
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 – skv Sep 08 '13 at 11:16

score 1 · Answer 2 · answered Sep 08 '13 at 10:51

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.

score 0 · Answer 3 · answered Sep 08 '13 at 12:26

0

An alternative way of doing this is to sort by the length and then the field:

order by char_length(col), col

answered Sep 08 '13 at 12:26

Gordon Linoff

3 Answers3