CODEWITHSUNDEEP
phpmysql
Aswathy S
Aswathy S

Reputation: 731

My sql sorting varchar field with numeric part

Hi I have a table data_table .I need to get all the title from the table.The title field (type varchar) contain alphanumeric data like below

title
---------
ab 2007
ab 2017
ba 2018
ba 2017
cd 2017
cd 2016

My desired output is 

 title
---------
 ba 2018
 ab 2017
 ba 2017
 cd 2017
 cd 2016
 ab 2007

I tried by CAST .But didnt get required result

SELECT title, CAST(title as SIGNED) AS casted_column FROM data_table ORDER BY `title` DESC

Upvotes: 0

Views: 39

Answers (2)

sumit
sumit

Reputation: 15464

Use RIGHT to get last 4 digits and then cast it to unsigned and sort it 

SELECT title 
from tbl
order by cast(right(title,4) as unsigned) desc, title desc

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

if your title end always with 4 digit you could substr for obtain the number and use in sort

      SELECT title
      FROM data_table
      ORDER BY substr(`title`, -4) DESC , title DESC

Upvotes: 1

Related Questions