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-06 08:49:07Z

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 6, 2012 at 8:49

answered Sep 6, 2012 at 8:08

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Robin Castlin Over a year ago

Clean function which seems to work, but I don't get how it does

Raphaël Althaus Over a year ago

@JohnWoo I made a mistake in the arguments order. Corrected. sqlfiddle.com/#!2/78de4/3

Raphaël Althaus Over a year ago

@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'

kasimir Over a year ago

Looks good, but result gets odd when I add more numbers: sqlfiddle.com/#!2/66c54/1. Any ideas on that?

Raphaël Althaus Over a year ago

@kasimir as pointed, I made a mistake in the argument order, if you take the actual answer, it will work sqlfiddle.com/#!2/66c54/2

|

Robin Castlin · Accepted Answer · 2012-09-06 08:12:37Z

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 6, 2012 at 8:12

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Comments

John Woo · Accepted Answer · 2012-09-06 08:28:34Z

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 6, 2012 at 8:28

answered Sep 6, 2012 at 8:08

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-09-06 08:58:49Z

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 6, 2012 at 8:58

answered Sep 6, 2012 at 8:09

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

2 Comments

Joe G Joseph Over a year ago

@kasimir:I have ipdated my answer Plz check now

kasimir Over a year ago

It works. But I think the LPAD solution is a bit more elegant (shorter).

Ariful Islam · Accepted Answer · 2012-09-06 08:10:03Z

0

ORDER BY CAST(`Result` AS SIGNED) DESC

this should work.

answered Sep 6, 2012 at 8:10

Ariful Islam

7,6837 gold badges38 silver badges55 bronze badges

1 Comment

kasimir Over a year ago

I had tried this one. This gives me reverse order: 3, 2, 1, DNF, DNS, I'm after 1, 2, 3, DNF, DNS.

Collectives™ on Stack Overflow

MySQL sort varchar column numeric, numbers first

5 Answers 5

8 Comments

Comments

SQLFiddle Demo

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related