CODEWITHSUNDEEP
mysql
Johnathan
Johnathan

Reputation: 21

MYSQL - groupings

Given the following mysql result set: 

 id      code       name               importance
'1234', 'ID-CS-B', 'Chocolate Sauce', '1',
'1122', 'ID-MC-A', 'Mint Choc Chip',  '5',
'5621', 'ID-MC-B', 'Mint Choc Chip',  '4',
'1221', 'ID-CS-A', 'Chocolate Sauce', '2',
'1212', 'ID-CS-C', 'Chocolate Sauce', '3',

There are two products here: Chocolate Sauce and Mint Choc Chip. I would like to pull a single instance from each product based on the importance.

I want the most important Mint Choc Chip product and the most important chocolate sauce product using the column "importance": the higher the value the more important

Upvotes: 1

Views: 46

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Try this as well,

select b.name, max(a.importance)
from t b
left join t a
on b.id = a.id
group by b.name
;

Results:

NAME                MAX(A.IMPORTANCE)
Chocolate Sauce     3
Mint Choc Chip      5

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

    SELECT x.*
      FROM my_table x
      JOIN 
         ( SELECT name, MAX(importance) max_importance FROM my_table GROUP BY name ) y
        ON y.name = x.name
       AND y.max_importance = x.importance;

Upvotes: 3

Related Questions