Im trying to find the person who has drank most beer types from USA. The result should be just be the name of that person alone and not include a count column. How should I perform the select statement? The result should look like this:
name Alan
The table above should be acquired from the table below:
sysnr beer country name ---------- ---------------- ---------- ---------- 1260 Guinness Draught Irland Agneta 11226 Gigantic IPA USA Alan 11410 Alesmith Decaden USA Alan 11581 Trashy Blonde Storbritan Alan 1260 Guinness Draught Irland Alan 1403 Tuborg Danmark Alan 1416 Lowenbrau Tyskland Alan 1506 Jever Tyskland Alan 1515 Punk IPA Storbritan Alan 1523 Armageddon IPA Nya Zeelan Alan 1540 Westmalle Double Belgien Alan 1548 Brooklyn Lager USA Alan 1553 Chang Beer Thailand Alan 1559 Coors Light USA Alan 1565 Bitburger Tyskland Alan 1565 Bitburger Tyskland Alan 1566 Pilsner Urquell Tjeckien Alan 1574 Pabst Blue Ribbo USA Alan 1585 San Miguel Spanien Alan 1594 Lapin Kulta Finland Alan 1625 Sierra Nevada Pa USA Alan 1642 Fullers London P Storbritan Alan 1649 Samuel Adams Bos USA Alan 1650 Orval Belgien Alan 1654 Duvel Belgien Alan 1657 Chimay vit Belgien Alan 1659 Leffe Blond Belgien Alan 1664 Kwak Belgien Alan 1670 DAB Tyskland Alan 1670 DAB Tyskland Alan 1675 Anchor Steam Bee USA Alan 89607 Lagunitas IPA USA Alan 89793 Maredsous Tripel Belgien Alan 11410 Alesmith Decaden USA Dick 1553 Chang Beer Thailand Dick 1642 Fullers London P Storbritan Dick 1222 Sofiero Sverige Dina 1574 Pabst Blue Ribbo USA Dina 1650 Orval Belgien Dina 11451 Pripps Bla Sverige Fredrik 1403 Tuborg Danmark Fredrik 1559 Coors Light USA Fredrik 30611 Dugges High Five Sverige Fredrik 11489 Gambrinus Tjeckien Henrik 1353 Budvar Tjeckien Henrik 1544 Litovel Classic Tjeckien Henrik 1566 Pilsner Urquell Tjeckien Henrik 1611 Breznak Tjeckien Henrik 89301 Bernard Tjeckien Henrik 11410 Alesmith Decaden USA Janne 1260 Guinness Draught Irland Janne 1506 Jever Tyskland Janne 1559 Coors Light USA Janne 1559 Coors Light USA Janne 1649 Samuel Adams Bos USA Janne 11410 Alesmith Decaden USA Johan 1515 Punk IPA Storbritan Johan 1548 Brooklyn Lager USA Johan 1559 Coors Light USA Johan 1670 DAB Tyskland Johan 1403 Tuborg Danmark Jonas 1403 Tuborg Danmark Juha 1403 Tuborg Danmark Juha 1522 Karhu Finland Juha 1523 Armageddon IPA Nya Zeelan Juha 1566 Pilsner Urquell Tjeckien Juha 1574 Pabst Blue Ribbo USA Juha 1594 Lapin Kulta Finland Juha 30023 US Red Ale Finland Juha 30658 Stigbergets Sais Sverige Juha 11433 Falcon Export Sverige Kalle 1519 Saxon Finland Kalle 1522 Karhu Finland Kalle 1551 Citra Pale Ale Holland Kalle 1594 Lapin Kulta Finland Kalle 1675 Anchor Steam Bee USA Kalle 30023 US Red Ale Finland Kalle 11433 Falcon Export Sverige Kjell 1515 Punk IPA Storbritan Kjell 1548 Brooklyn Lager USA Kjell 1559 Coors Light USA Kjell 11226 Gigantic IPA USA Lennart 11451 Pripps Bla Sverige Lennart 11489 Gambrinus Tjeckien Lennart 11581 Trashy Blonde Storbritan Lennart 1344 Amstel Holland Lennart 1403 Tuborg Danmark Lennart 1407 Backyard Brew Danmark Lennart 1523 Armageddon IPA Nya Zeelan Lennart 1540 Westmalle Double Belgien Lennart 1565 Bitburger Tyskland Lennart 1566 Pilsner Urquell Tjeckien Lennart 1574 Pabst Blue Ribbo USA Lennart 1594 Lapin Kulta Finland Lennart 1642 Fullers London P Storbritan Lennart 1650 Orval Belgien Lennart 1659 Leffe Blond Belgien Lennart 1664 Kwak Belgien Lennart 1670 DAB Tyskland Lennart 89793 Maredsous Tripel Belgien Lennart 1403 Tuborg Danmark Lisen 1407 Backyard Brew Danmark Lisen 1548 Brooklyn Lager USA Lisen 1553 Chang Beer Thailand Lisen 1565 Bitburger Tyskland Lisen 1594 Lapin Kulta Finland Lisen 1657 Chimay vit Belgien Lisen 30611 Dugges High Five Sverige Lisen 30658 Stigbergets Sais Sverige Lisen 11410 Alesmith Decaden USA Magnus 1260 Guinness Draught Irland Magnus 1407 Backyard Brew Danmark Maria 11451 Pripps Bla Sverige Marie 11489 Gambrinus Tjeckien Rikard 1353 Budvar Tjeckien Rikard 1540 Westmalle Double Belgien Rikard 1544 Litovel Classic Tjeckien Rikard 1611 Breznak Tjeckien Rikard 1650 Orval Belgien Rikard 1654 Duvel Belgien Rikard 1657 Chimay vit Belgien Rikard 1659 Leffe Blond Belgien Rikard 1664 Kwak Belgien Rikard 1670 DAB Tyskland Rikard 89793 Maredsous Tripel Belgien Rikard 11410 Alesmith Decaden USA Urban 1416 Lowenbrau Tyskland Urban 1506 Jever Tyskland Urban 1565 Bitburger Tyskland Urban 1642 Fullers London P Storbritan Urban 1670 DAB Tyskland Urban
Appriciate the help
Advertisement
Answer
Use GROUP BY, ORDER BY and LIMIT if you want one result (even when there are ties):
select name -- , count(*) you don't need the count(*) here, but I would keep it from t where country = 'USA' group by name order by count(*) desc limit 1;
If you want all rows when there are ties, then use window functions:
select name
from (select name, count(*) as cnt,
rank() over (partition by country order by count(*) desc) as seqnum
from t
where country = 'USA'
group by name
) t
where seqnum = 1;