CODEWITHSUNDEEP
mysql
GcalD
GcalD

Reputation: 3

Natural sort doesn't arrange data properly

So I have this set of data for example:

+--------------+-----------+
| Name         | Money     |
+--------------+-----------+
| Mickey Mouse | 1,000,780 |
+--------------+-----------+
| Donald Duck  | 1,580,870 |
+--------------+-----------+
| Goofy        | 230,570   |
+--------------+-----------+

and when I use:

SELECT name, money FROM worth ORDER BY LENGTH(money), money

It still displays Goofy over Mickey and Donald.

and when I use:

SELECT name, money FROM worth ORDER BY money +0 ASC

It still displays Mickey over Donald. Where as Donald should be on top.

I want to arrange it descending order so Donald-Mickey-Goofy.

Upvotes: 0

Views: 37

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Storing money as a varchar is a bad idea for all sorts of reasons - convert it to a more appropriate format if at all possible.

Otherwise, this question has an answer.

Upvotes: 1

skelwa
skelwa

Reputation: 585

I would suggest storing column 'money' as Decimal or Double rather than VARCHAR. But for your case, you can order by on length descending first and then money descending.

The below query should work for you.

SELECT name, money FROM worth ORDER BY LENGTH(money) desc, money desc;

Upvotes: 0

Related Questions