ORDER BY DESC is not working right

Question

I have this sql query

SELECT `price` FROM `used_cars` ORDER BY `price` DESC

So I obviously want to order by price from high to low. However, it seems to be taking the first digit and sorting by that. My theory is that it is treating it like a string, and as the column is a varchar it makes sense. However, this is third party data, so I am stuck with it. How can I order so that the larger numbers come first?

So this is an example of how they are ordered

See http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql for how to cast to an int — Palpatim, May 01 '14 at 22:59
This is expected behavior for a text column that should be a decimal. To change it, first back up your database, then run the following command: ALTER TABLE user_cars MODIFY COLUMN price DECIMAL(10,2); — Tim Burch, May 01 '14 at 23:01
@TimBurch is right - you need to change the type of your data. You say that this is third-party data, but it's obviously in _your_ database... are you just uploading data from a third-party file? Nothing's preventing you from loading to a temp-file first, then casting when going to the final destination. Numeric data should be stored in a numeric column. — Clockwork-Muse, May 02 '14 at 00:08

score 11 · Answer 1 · answered May 01 '14 at 23:03

11

You should convert the column to a numeric data type. You can do that in the table definition, or in the query itself, for example with:

... ORDER BY `price`+0 DESC

answered May 01 '14 at 23:03

Joni

108,737
14
143
193

This is the best answer. The type of the column should be changed to decimal. – Tim Burch May 01 '14 at 23:03

score 6 · Accepted Answer · answered May 01 '14 at 23:00

6

CAST should work:

SELECT CAST(price AS UNSIGNED) AS NumPrice
FROM used_cars
ORDER BY NumPrice DESC

answered May 01 '14 at 23:00

Dave Jemison

694
3
7

score 2 · Answer 3 · answered May 01 '14 at 22:59

2

This should work:

(...)
ORDER BY CAST (price AS INT)

answered May 01 '14 at 22:59

wvdz

16,251
4
53
90

Rather than resort to casting, which is inefficient, the type of the column should be changed to decimal. – Tim Burch May 01 '14 at 23:04

zoruc · Answer 4 · 2014-05-02T01:23:41.250

-1

This will work but is bad performance

SELECT price FROM used_cars ORDER BY CAST(price AS int) DESC

edited May 02 '14 at 01:23

answered May 01 '14 at 23:00

zoruc

819
7
13

`CAST(price AS int)` is not working while CAST(price AS UNSIGNED) working – Maha Dev Dec 03 '18 at 08:43

ORDER BY DESC is not working right

4 Answers4