0

I need to sort a query's results by a varchar that may contain numbers, but also sort by other fields beforehand.

Say my table looks like:

+------------------------+-----------------+
| AnotherField           | VarCharWithNums |
+------------------------+-----------------|
| Same Data in Every Row | Numbers 5-10    |
| Same Data in Every Row | Numbers 10-13   |
| Same Data in Every Row | Numbers 13-15   |
+------------------------+-----------------|

This query:

SELECT VarCharWithNums, AnotherField
FROM MyTable
ORDER BY CAST(VarCharWithNums AS UNSIGNED) ASC

Gives me this:

+------------------------+-----------------+
| AnotherField           | VarCharWithNums |
+------------------------+-----------------|
| Same Data in Every Row | Numbers 5-10    |
| Same Data in Every Row | Numbers 10-13   |
| Same Data in Every Row | Numbers 13-15   |
+------------------------+-----------------|

This query:

SELECT VarCharWithNums, AnotherField
FROM MyTable
ORDER BY AnotherField ASC, CAST(VarCharWithNums AS UNSIGNED) ASC

Gives me this:

+------------------------+-----------------+
| AnotherField           | VarCharWithNums |
+------------------------+-----------------|
| Same Data in Every Row | Numbers 10-13   |
| Same Data in Every Row | Numbers 5-10    |
| Same Data in Every Row | Numbers 13-15   |
+------------------------+-----------------|

It doesn't matter what priority I give the fields in the ORDER BY clause, it never sorts VarCharWithNums correctly when I order it alongside other fields.

Recovering Nerdaholic
  • 687
  • 1
  • 7
  • 23
  • http://stackoverflow.com/questions/5417381/mysql-sort-string-number – Jen R May 08 '17 at 16:07
  • You probably will need to parse out that field to get only one number and use that for sorting (or two, depending one how you want the ranges to sort) – Jen R May 08 '17 at 16:08
  • If the same data is in every row of `AnotherField`, why would you want to sort on it? Or is that just part of the contrived example? I have no problems when I set up a test case and try it. – Brian A. Henning May 08 '17 at 16:11
  • Look here, too: http://stackoverflow.com/questions/4686849/sorting-varchar-field-numerically-in-mysql?rq=1 – Jen R May 08 '17 at 16:33

1 Answers1

0

You mentioned it in your last paragraph, but truly the only error in what you've shown is that, to get the sort you described, CAST(VarCharWithNums AS UNSIGNED) ASC needs to be the first thing in the ORDER BY clause. It should work, and it works when I create a contrived example on my machine.

Brian A. Henning
  • 1,374
  • 9
  • 24