0

I'm still very new to SQL queries and am looking for a (supposedly) relatively simple solution to an issue I'm having:

Say I have two relational tables:

+--------+------------+           +--------+--------+-------------+-------+
| TypeNr | TypeName   |           | BeerNr | TypeNr | BeerName    |  ABV  |
+--------+------------+           +--------+--------+-------------+-------+
|   1    |  Lager     |           |   1    |   1    | Budweiser   |  5    |
|   2    |  IPA       |           |   2    |   2    | Goose IPA   |  5.9  |
|   3    |  Trappiste |           |   3    |   2    | Goose So-Lo |  3    |
+--------+------------+           |   4    |   2    | Lost Palate |  6.3  |
                                  |   5    |   3    | Rochefort 8 |  8    |
                                  |   6    |   3    | Rochefort 10|  10   |
                                  +--------+--------+-------------+-------+

I'd like to return TypeName, BeerName and ABV of the row having the highest ABV for each respective TypeNr.

Normally I'd just query something like this

SELECT table1.TypeName, MAX(table2.ABV)
FROM table1
INNER JOIN table2 ON table1.TypeNr = table2.TypeNr
GROUP BY table1.TypeName

Which would return:

Lager      5
IPA        6.3
Trappiste  10

What alternative can I use here so the query also returns the correct BeerName associated with the highest ABV?

Lager      Budweiser     5
IPA        Lost Palate   6.3
Trappiste  Rochefort 10  10

I feel I can no longer rely on GROUP BY when attempting this, because I'd be forced to also group by BeerName (since I'm also selecting it) which does not produce the result I want.

NathanC
  • 61
  • 3

0 Answers0