Search code examples
sqlmysqlnatsort

Problem with MySQL ORDER BY - can I use natsort to fix it?

I need to use natural sorting with a MySQL result since it currently sorts my INT values as follows:

  • 1
  • 123
  • 1256
  • 22
  • 231

[etc]

While I would need it to sort like

  • 1
  • 22
  • 231
  • 1256

I know of the "natsort" function, but it does not work for a MySQL query result. How can I sort (naturally), is it possible to do this within the query?

Thanks for the help!

EDIT: Example query $result = mysql_query("SELECT * FROM forums ORDER BY 'posts' DESC;");

Not entirely sure if my use of DESC there is valid, but it doesn't throw an error so it must be fine. Unfortunately, it seems changing DESC to ASC also has no effect...

Solution

  • The query you've posted there is sorting by a constant string expression, rather than a column name or position. Either use backticks to quote, or take out the quotes:

    SELECT * FROM forums ORDER BY `posts` DESC

    or maybe

    SELECT * FROM forums ORDER BY posts DESC

    Would explain why changing between ASC and DESC has no effect.

    Initial Answer Was:

    The sort order you describe suggests the INT values are actually being stored as a character type (or maybe converted to character before sorting).

    Check whether you are storing the data in a numeric or character type. If possible use a numeric type, then the natural sort order will be as you require. If you can't change the underlying data type to be INT, then you can do this in your query (e.g. using CAST), probably at cost of performance.