Justin Blair Justin Blair - 2 years ago 131
MySQL Question

Order by last 3 chars

I have a table like:

id name
--------
1 clark_009
2 clark_012
3 johny_002
4 johny_010


I need to get results in this order:

johny_002
clark_009
johny_010
clark_012


Do not ask me what I already tried, I have no idea how to do this.

Answer Source

This will do it, very simply selecting the right-most 3 characters and ordering by that value ascending.

SELECT *
FROM table_name
ORDER BY RIGHT(name, 3) ASC;

It should be added that as your data grows, this will become an inefficient solution. Eventually, you'll probably want to store the numeric appendix in a separate, indexed integer column, so that sorting will be optimally efficient.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download