Help with MySQL query... Need help ordering a group of rows

Question

I can tell it best by explaining the query I have, and what I need.

I need to be able to get a group of items from the database, grouped by category, manufacturer, and year made. The groupings need to be sorted based on total amount of items within the group. This part is done with the query below.

Secondly, I need to be able to show an image of the most expensive item out of the group, which is why I use MAX(items.current_price). I thought MAX() gets the ENTIRE row corresponding to the largest column value. I was wrong, as MAX only gets the numeric value of the largest price. So the query doesnt work well for that.

SELECT
    items.id,
    items.year, 
    items.manufacturer,
    COUNT(items.id) AS total,
    MAX(items.current_price) AS price,
    items.gallery_url,
FROM 
    ebay AS items
WHERE
    items.primary_category_id = 213
AND
    items.year <> ''
AND
    items.manufacturer <> ''
AND
    items.bad_item <> 1
GROUP BY  
    items.primary_category_id,
    items.manufacturer,
    items.year
ORDER BY  
    total DESC,
    price ASC
LIMIT 
    10

if that doesnt explain it well, the results should be something like this

id 10548
year 1989
manufacturer bowman
total 451
price 8500.00 (The price of the most expensive item in the table/ not the price of item 10548)
gallery_url http://ebay.xxxxx (The image of item 10548)

A little help please. Thanks

Answer 1

I've had this same problem, and I'm fairly certain you have to do two queries (or a subquery, that's a matter of taste).

The first query is like what you have (except id isn't helping you). The second query uses the GROUP BY fields and one (one!) MAX field to get the id and any other meta-data you need.

I believe this is the implementation, although it's hard to test:

SELECT
    items.id,
    items.year, 
    items.manufacturer,
    items.gallery_url
FROM
    ebay as items
NATURAL JOIN
    (
        SELECT
            COUNT(items.id) AS total,
            MAX(items.current_price) AS current_price,
            items.primary_category_id,
            items.manufacturer,
            items.year
        FROM 
            ebay AS items
        WHERE
            items.primary_category_id = 213
        AND
            items.year <> ''
        AND
            items.manufacturer <> ''
        AND
            items.bad_item <> 1
        GROUP BY  
            items.primary_category_id,
            items.manufacturer,
            items.year
        ORDER BY  
            total DESC,
            price ASC
        LIMIT 
            10
    ) as bigones
ORDER BY  
    bigones.total DESC,
    bigones.current_price ASC

---

This documentation may help you understand what's going on:

http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html

... all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.