mysqlgroup-bymax

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


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

Solution

  • 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