CODEWITHSUNDEEP
mysql
kasimir
kasimir

Reputation: 1554

MySQL sort varchar column numeric, numbers first

I've got a varchar column that I want to sort numeric, which works great when using this trick: https://stackoverflow.com/a/5418033/1005334 (in short: ...ORDER BY Result * 1).

However, the table concerned contains results. So something like this occurs:

Result
------
DNS
DNF
1
2
3

The numbers are correctly ordered, but the DNF comes above the numbers when sorting like this. What I'd like is to have the numeric sort, but with non-numbers sorted alphabetically below the numbers. Like so:

Result
------
1
2
3
DNF
DNS

In what way can I modify the query (preferably only the ORDER BY clause) to get this result?

Upvotes: 3

Views: 5307

Answers (5)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Please change your ORDER BY Clause with this:

  ORDER BY
  CASE WHEN Result REGEXP '^[0-9]+$' THEN Result*1 else 999999 END,
       Result

This will order the numeric values first then the rest

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

use LPAD

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad

LPAD(yourField, 20, '0');

this will manage correct order for "varchar numeric fields" (10 will be after 2) and put strings at the end.

SqlFiddle

The second argument (20) is quite arbitrary. It should be equivalent to (or bigger then) the length of the longest string in your field.

Upvotes: 4

John Woo
John Woo

Reputation: 263693

You can do that by using MySQL's REGEXP. Try this one,

SELECT *
FROM tablea
ORDER BY  IF(`Result` REGEXP '^-?[0-9]+$', 0, 1) ASC,
          `Result` ASC

SQLFiddle Demo

Upvotes: 1

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT *, (Result REGEXP '^[0-9]+$') AS is_numeric
FROM table_name
ORDER BY is_numeric DESC, 
    CASE WHEN is_numeric THEN (Result + 0) ELSE Result END ASC

Upvotes: 2

Ariful Islam
Ariful Islam

Reputation: 7675

ORDER BY CAST(`Result` AS SIGNED) DESC

this should work.

Upvotes: 0

Related Questions