MySQL sort varchar column numeric, numbers first

Question

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?

Raphaël Althaus · Accepted Answer · 2012-09-06T08:49:07.353

4

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.

edited Sep 06 '12 at 08:49

answered Sep 06 '12 at 08:08

Raphaël Althaus

59,727
6
96
122

Clean function which seems to work, but I don't get how it does – Robin Castlin Sep 06 '12 at 08:23
@JohnWoo I made a mistake in the arguments order. Corrected. http://sqlfiddle.com/#!2/78de4/3 – Raphaël Althaus Sep 06 '12 at 08:26
@RobinCastlin well, it's just add third argument('0') at the left of a string, x times (where x = second argument (20) - length of string). So if second argument is '3', 1 becomes '001' and '100' becomes '100' Then it's just a "string sort' – Raphaël Althaus Sep 06 '12 at 08:29
Looks good, but result gets odd when I add more numbers: http://sqlfiddle.com/#!2/66c54/1. Any ideas on that? – kasimir Sep 06 '12 at 08:45
@kasimir as pointed, I made a mistake in the argument order, if you take the actual answer, it will work http://sqlfiddle.com/#!2/66c54/2 – Raphaël Althaus Sep 06 '12 at 08:46
Just read the comments, seems it does fine now: http://sqlfiddle.com/#!2/66c54/2. Let me test a bit more in my own setup. – kasimir Sep 06 '12 at 08:48
A question: why is the `'0'` quoted and the `20` not? – kasimir Sep 06 '12 at 08:55
@kasimir : '0' is the character added at the left. 20 is the length of the resulting string. Look at the doc (link given in answer) for a more complete explanation (BTW, you could remove quotes on '0' in this case, but it makes things clearer) – Raphaël Althaus Sep 06 '12 at 08:58

score 2 · Answer 2 · answered Sep 06 '12 at 08:12

2

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

answered Sep 06 '12 at 08:12

Robin Castlin

10,956
1
28
44

John Woo · Answer 3 · 2012-09-06T08:28:34.883

1

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

edited Sep 06 '12 at 08:28

answered Sep 06 '12 at 08:08

John Woo

258,903
69
498
492

Joe G Joseph · Answer 4 · 2012-09-06T08:58:49.513

1

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

edited Sep 06 '12 at 08:58

answered Sep 06 '12 at 08:09

Joe G Joseph

23,518
5
56
58

@kasimir:I have ipdated my answer Plz check now – Joe G Joseph Sep 06 '12 at 08:59
It works. But I think the `LPAD` solution is a bit more elegant (shorter). – kasimir Sep 06 '12 at 09:09

score 0 · Answer 5 · answered Sep 06 '12 at 08:10

0

ORDER BY CAST(`Result` AS SIGNED) DESC

this should work.

answered Sep 06 '12 at 08:10

Ariful Islam

7,639
7
36
54

I had tried this one. This gives me reverse order: `3, 2, 1, DNF, DNS`, I'm after `1, 2, 3, DNF, DNS`. – kasimir Sep 06 '12 at 08:38

MySQL sort varchar column numeric, numbers first

5 Answers5

SQLFiddle Demo