GROUP BY & ORDER BY - Ordering by a specific field

I need to write a query which returns a list of unique 'classes' ordered by the most popular item in that class. Here's an example query:

SELECT items.title, items.popularity 
FROM items, classes 
WHERE class_id = classes.id 
GROUP BY class_id 
ORDER BY MAX(items.popularity) DESC

This query returns the correct class but the item it returns is the first that appears in the table rather than the most popular.

开发者_StackOverflow

Similarly I have tried:

SELECT MAX(items.title), items.popularity 
FROM items, classes 
WHERE class_id = classes.id 
GROUP BY class_id 
ORDER BY MAX(items.popularity) DESC

And all variations - this just returns the most recent added and not the most popular.

I've read lots and lots of other answers.

I'm sure there's a simple answer, please help!

---

You are not selecting anything from classes, so don't need that table:

select i.*
from (
    SELECT class_id, max(popularity) as MaxPopularity
    FROM items 
    GROUP BY class_id  
) ip
inner join items i on ip.class_id = i.class_id 
    and ip.MaxPopularity = i.popularity

Note: You may get duplicates if there is more than one item in a class with the same popularity.

---

my guess is you should order the MAX(popularity) in descending order, try adding DESC at the end of the query.

---

I haven't try it but I think it should work...

SELECT MAX(items.title), items.popularity 
FROM items, classes 
WHERE class_id = classes.id 
GROUP BY class_id 
ORDER BY items.popularity DESC

---

SELECT items.title, items.popularity 
FROM items, classes 
WHERE class_id = classes.id 
ORDER BY items.popularity DESC

You shouldn't need the group by as you are already selecting a specific class in the where statement. Additionally, the DESC in the order by should take care of the order without the need for the MAX.