Agustina Tossi Agustina Tossi -4 years ago 121
SQL Question

Select max() of every value mariaDB

I have a table that looks like this:

name price class
a 10 x
a 20 y
a 15 z
b 40 y
b 35 z
c 5 x
c 10 y
c 15 z
c 12 w


i want to get for every name which is the highest price and to wich class it belongs. something like this:

name price class
a 20 y
b 40 y
c 15 z


I have tried this:

SELECT name, max(price), class
from t
GROUP by name


but it brings me the wrong class. i get this:

name price class
a 20 x
b 40 y
c 15 x


can you tell me what am i doing wrong?

Answer Source

This should give you what you're looking for:

SELECT T.*
FROM T
    INNER JOIN (SELECT Name,
                    MAX(price) maxPrice
                FROM t
                GROUP BY Name) tMax on T.Name = tMax.Name
                                           AND T.Price = tMax.maxPrice
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download