Ordering integers in a database

Question

I have a request like this

SELECT user_id FROM user_rights ORDER BY user_id DESC; 

Why does this give me these spoiled figures?

 USER_ID 4 4 4 4 3 3 21 21 21 21 21 21 20 20 

It does not order the numbers as expected.

Proof: http://sqlfiddle.com/#!2/c753a/1

I understand that they can be ordered in alphabetical order or in numbers, buy, I do not understand why this result is so strange ...

Answer 1

This is because user_id not numeric.

SPECIFY it

 SELECT user_id FROM user_rights ORDER BY CAST(user_id as SIGNED) DESC; 

SQLFiddle Demo

Another solution is changing the data type of your user_id column to int

Answer 2

The userid varchar(64) field varchar(64) , so it is sorted alphabetically, in descending order (at the request of the SQL query). Given this, your result is exactly correct.

You must change your userid data type or apply it to an integer if you can guarantee that it will always be an integer.

Answer 3

hey, if there are several user records in the table, than you can use the group and try to do the order, for example, SELECT user_id FROM user_rights GROUP BY user_id ORDER BY CONVERT(int,user_id) DESC;