CODEWITHSUNDEEP
sqlmysql
mrpatg
mrpatg

Reputation: 10117

mysql show rows in order from highest value to lowest value

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.

Upvotes: 0

Views: 8878

Answers (5)

Pir Fahim Shah
Pir Fahim Shah

Reputation: 10623

If you want to show your table rows from higher value to lower value then use this query.

query = "SELECT * FROM CurrencyTable ORDER BY CAST(currency_rate AS UNSIGNED) DESC";

And if you want to show rows from lower value to higher value then use this query

query-2 = "SELECT * FROM CurrencyTable ORDER BY CAST(currency_rate AS UNSIGNED) ASC";

Upvotes: 0

Nate
Nate

Reputation: 19030

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.

Upvotes: 1

Glen
Glen

Reputation: 22290

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

Upvotes: 1

Björn
Björn

Reputation: 29381

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?

Upvotes: 3

Rob Hruska
Rob Hruska

Reputation: 120286

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.

Upvotes: 3

Related Questions