Search code examples
sqlmariadbgreatest-n-per-group

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 which 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?

Solution

  • 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