To keep it short, I have found a tutorial online and followed to the point: http://www.tizag.com/mysqlTutorial/mysqlmax.php
SELECT type, MAX(price) FROM products GROUP BY type
My question is: How do I echo which "clothing" is the most expensive (In this case "Blouse")?
UPDATE:
Sorry guys, my bad. I needed to make myself more clear. What I am looking for is a solution that shows each "name" where they are most expensive:
name type price
Clothing Blouse 34.97
Toy Playstation 89.95
Music Country Tunes 21.55
Try the following query:
Solution #1:
SELECT
products.name,
products.type,
products.price
FROM products
INNER JOIN
(
SELECT type,MAX(price) max_price
FROM products
GROUP BY type ) t
ON products.type = t.type
AND products.price = t.max_price;
Solution #2:
SELECT
products.name,
products.type,
products.price
FROM
products
WHERE (type, price) IN (
SELECT type, MAX(price) max_price
FROM products
GROUP BY type )
EDIT:
Note: Both solutions might give you multiple products under same type
if they share the same maximum
price.
If you strictly want at most one item from each type then you need to group by
again in the last line.
So for both solutions the last line would be:
GROUP BY products.type, products.price