Help with MySQL query... Need help ordering a group of rows
- by user156814
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
