php mysql order by numbers from right of title

Question

I need order by Numbers from end of title but need as UNSIGNED result, i try to get last 5 digit from title table and sort but not giving natural result

title table

title1 (2015)
something (1999)
title1 (1994
title1 (2014)

My code

 ORDER BY RIGHT(title,5) DESC

Result is

something (1999)
title1 (1994)
title1 (2014)
title1 (2015)

How can i Order like this:

title1 (2015)
title1 (2014)
something (1999)
title1 (1994)

Now this code order like 1,10,101,2,20,201 but i need like this 1,2,10,20,101,201

Now this code order like it order 1,10,101,2,20,201 like this i need like this 1,2,10,20,101,201 — Raj Mohammed, Jun 01 '15 at 14:24
You should store years in separate column like `title | year`, data: `title1 | 1994`, `something | 1999`, etc. — pavel, Jun 01 '15 at 14:45

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

Because you substring, it converts the data to text.

You need to convert it back to number (source):

order by substring(title,-5,4)*1 desc;

edited May 23 '17 at 12:21

Community

1
1

answered Jun 01 '15 at 14:50

NotJustin

529
6
20

score 0 · Answer 2 · answered Jun 01 '15 at 14:22

0

You need ORDER BY ASC and not DESC.

ORDER BY RIGHT(title,5) ASC

Learn more here.

answered Jun 01 '15 at 14:22

Andrea php

63
2
8

Now this code order like it order 1,10,101,2,20,201 like this i need like this 1,2,10,20,101,201 – Raj Mohammed Jun 01 '15 at 14:27
You have only one column in your table? I think for output what you like you need alter your table, and add another column. – Andrea php Jun 01 '15 at 14:30
Any other idea using ORDER BY RIGHT?? – Raj Mohammed Jun 01 '15 at 14:30
Is a problem in your program do another column? I think is the best easier way to output what you like. Create another column, example date, and order by date. – Andrea php Jun 01 '15 at 14:32
it is not a date it is year comes with title not auto increment – Raj Mohammed Jun 01 '15 at 14:33

score 0 · Accepted Answer · answered Jun 01 '15 at 14:42

0

What if you try like this. See a DEMO HERE

select col1 from table1
order by replace(right(col1,5), ')','') desc;

answered Jun 01 '15 at 14:42

Rahul

76,197
13
71
125

php mysql order by numbers from right of title

3 Answers3