Order by last 3 chars

Question

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 1

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.