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.