Sanju Menon Sanju Menon - 8 days ago 5
MySQL Question

MySQL order by clause with varchar type in the mysql query

I am trying to write a mysql query. One data field called 'fileno' with varchar type and the data is in the following way:

===================
id | fileno
===================
1 | 16/12
2 | 15/91
3 | 15/70
4 | 16/07
===================


Now i need to order the fields in descending order and i am expecting the fields to be ordered in descending order. But not working fine. The expected order form is below:

16/12
16/07
15/91
15/70


I tried the below query:

SELECT
id,
fileno
FROM
customer_request
ORDER BY
fileno DESC

Answer
SELECT id, fileno
FROM yourTable
ORDER BY CAST(SUBSTRING(fileno, INSTR(fileno, '/') + 1) AS UNSIGNED)    DESC,
         CAST(SUBSTRING(fileno, 1, INSTR(fileno, '/') - 1) AS UNSIGNED) DESC

Demo here:

SQLFiddle

Comments