CODEWITHSUNDEEP
mysqlsqlsql-order-by
maddo7
maddo7

Reputation: 4963

Two ORDER BY clauses and a LIMIT

This is some sorting: I have a table with ids, names and ratings. I want to list the 10 best items based on ratings, that's fine:

SELECT id FROM items ORDER BY ratings DESC LIMIT 10

But now here comes the hard part: I want to list the 10 best items based on ratings but order them alphabetically. I tried

SELECT id FROM items ORDER BY ratings DESC, names ASC LIMIT 10

and

SELECT id FROM items ORDER BY names ASC, ratings DESC  LIMIT 10

but neither gives the desired result.

Upvotes: 1

Views: 2356

Answers (2)

sgeddes
sgeddes

Reputation: 62831

You could use a subquery:

SELECT *
FROM (
   SELECT id, names
   FROM items 
   ORDER BY ratings DESC
   LIMIT 10
   ) t
ORDER BY names

EDIT: Further Explanation

Your original query sorts by ratings DESC, and then by names -- it would only sort the names with the same ratings. So if two items had the same rating, then you'd get those 2 sorted by name. It's going to sort all your records by rating first, and then by name afterwards (within the same ratings).

Upvotes: 9

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

SELECT id, name, rating
FROM
    (SELECT id, name, rating FROM items ORDER BY ratings LIMIT 10) t
ORDER BY name

This will take top 10 by rating, and then sort by name.

Your queries are incorrect because: They sorts rows by names and then by ratings (or in oposite order), taking top10 after the sort is performed. You need to make the first sort, take top 10 and then sort these 10 results using second column values.

Upvotes: 3

Related Questions