mysql show rows in order from highest value to lowest value

Question

I have a table that contains a column named views. Each time the user refreshes the page, it updates the view count +1.
What im trying to do, is create a mysql query to output the list of rows based on their views count, from highest to lowest.

this is what i thought would work

SELECT * FROM picdb ORDER BY views DESC LIMIT 10

even when i view it ASCENDING, its all out of whack.

thoughts?

EDIT the column type is TEXT

SOLVED changed column type to INT and it works fine now. Thanks for pointing it out.

Answer 1

If your column type is TEXT, the default sorting behavior treats the data as strings, and therefore sorts them alphabetically (not numerically).

Change your column type to a number type, and it will sort correctly.

Answer 2

SELECT
    *
FROM
    tbl
ORDER BY
    CAST(views AS UNSIGNED) DESC
LIMIT
    10

Might do the trick. The real question is why you have a column containing integers with the text type?

Answer 3

select a,b,CAST(views AS UNSIGNED) c from picdb order by c desc limit 10

Answer 4

This looks like the classic alphanumeric sort problem. If the column type is text, then you are probably seeing order like 1, 10, 15, 20, 3. In other words, it’s sorting by the text value instead of the integer value.

You could change the column to a numeric type, or you could do:

SELECT * FROM picdb ORDER BY CONVERT(views, UNSIGNED INTEGER) DESC LIMIT 10

However, this would not be able to take advantage of any index on the views column. It would be better to change the column to a numeric type if that is indeed what it represents.