select max, group by and display other column that's not in group by clause

mysql group-by max

11,798

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;

Demo Here

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 )

See Demo

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

See Demo of it

11,798

Author by

CitronAutobot

Updated on August 04, 2022

Comments

CitronAutobot almost 2 years
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
```
Matt Raines about 8 years

This will return multiple rows if more than one product has the most expensive price. This might, or might not, be the desired behaviour.
Matt Raines about 8 years

This will return only one row (picked arbitrarily) if more than one product has the most expensive price. This might, or might not, be the desired behaviour.
1000111 about 8 years

You may get other types in your result if those have the same price as the max price of clothing.
Matt Raines about 8 years

This might return a product that isn't in the "Clothing" type if it's more expensive than the most expensive "Clothing".
1000111 about 8 years

Please check this query. Look I've added two queries. Both will give you the same result. Check the demo too. @CitronAutobot
CitronAutobot about 8 years

The first solution seems to be working, I have no idea how or why - but thnx alot :)
1000111 about 8 years

If you don't know how INNER JOIN works then it would be difficult to understand. That's why I've added the second solution. You need to understand mysql IN for that.
CitronAutobot about 8 years

This is almost the solution. How can I get more than one row?
Matt Raines about 8 years

These queries will both return multiple rows if more than one product has the most expensive price in each type. This might, or might not, be the desired behaviour. For example, if a PlayStation and an XBox both cost £89.95, there will be two rows returned for the "Toy" type.
1000111 about 8 years

Yes you are right @MattRaines. Actually it was my intention. I didn't want to ignore the second item having the same maximum price under the same type. If really want to ignore the second one then need to add another group by as the final line.
1000111 about 8 years

Thanks for your opinion @MattRaines. Please look at the EDIT section of my answer.
Gary Chen over 3 years

Second solution is more recommendable