I have a table in MYSQL with a column called league.

When I try to sort the values using this query I cant get it to sort correctly.

SELECT DISTINCT (
t.league
)
FROM teams t
GROUP BY t.league
ORDER BY t.league ASC 

However with this I get:

11a12
13
14
15
16a17
5a7
8a10

it looks like it sorts all of them correctly except the last two

Any ideas?

thanks

• mysql
• sql
• sql-order-by

Yes, the column must be of a string type and 5 comes AFTER 11. You might consider changing those fields (which seem to be multivaluated) into 2 separate numeric fields (or even 3 if that a value makes any difference)

No, they are sorted lexicographically rather than numerically:

11a12
13
14
15
16a17
5a7        # 5 is greater than 1
8a10

If you want to sort them numerically, you can use things like:

order by (t.league + 0)
order by cast (t.league, int)

but I'm not sure how they'll handle the non-numerics in the field. You may want to look into using the lpad function:

order by lpad (t.league, 20, '0')

which will pad the field to a specific size by prefixing it with 0 characters.

Just keep in mind that per-row functions rarely scale well so, if you want to maintain performance as the table grows in size, you may want to do something like split the column into components, making the numeric ones non-text. Depending on your specific circumstances, that may or may not work.