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:
x
nameAlanThe table above should be acquired from the table below:
sysnr beer country name---------- ---------------- ---------- ----------1260 Guinness Draught Irland Agneta11226 Gigantic IPA USA Alan11410 Alesmith Decaden USA Alan11581 Trashy Blonde Storbritan Alan1260 Guinness Draught Irland Alan1403 Tuborg Danmark Alan1416 Lowenbrau Tyskland Alan1506 Jever Tyskland Alan1515 Punk IPA Storbritan Alan1523 Armageddon IPA Nya Zeelan Alan1540 Westmalle Double Belgien Alan1548 Brooklyn Lager USA Alan1553 Chang Beer Thailand Alan1559 Coors Light USA Alan1565 Bitburger Tyskland Alan1565 Bitburger Tyskland Alan1566 Pilsner Urquell Tjeckien Alan1574 Pabst Blue Ribbo USA Alan1585 San Miguel Spanien Alan1594 Lapin Kulta Finland Alan1625 Sierra Nevada Pa USA Alan1642 Fullers London P Storbritan Alan1649 Samuel Adams Bos USA Alan1650 Orval Belgien Alan1654 Duvel Belgien Alan1657 Chimay vit Belgien Alan1659 Leffe Blond Belgien Alan1664 Kwak Belgien Alan1670 DAB Tyskland Alan1670 DAB Tyskland Alan1675 Anchor Steam Bee USA Alan89607 Lagunitas IPA USA Alan89793 Maredsous Tripel Belgien Alan11410 Alesmith Decaden USA Dick1553 Chang Beer Thailand Dick1642 Fullers London P Storbritan Dick1222 Sofiero Sverige Dina1574 Pabst Blue Ribbo USA Dina1650 Orval Belgien Dina11451 Pripps Bla Sverige Fredrik1403 Tuborg Danmark Fredrik1559 Coors Light USA Fredrik30611 Dugges High Five Sverige Fredrik11489 Gambrinus Tjeckien Henrik1353 Budvar Tjeckien Henrik1544 Litovel Classic Tjeckien Henrik1566 Pilsner Urquell Tjeckien Henrik1611 Breznak Tjeckien Henrik89301 Bernard Tjeckien Henrik11410 Alesmith Decaden USA Janne1260 Guinness Draught Irland Janne1506 Jever Tyskland Janne1559 Coors Light USA Janne1559 Coors Light USA Janne1649 Samuel Adams Bos USA Janne11410 Alesmith Decaden USA Johan1515 Punk IPA Storbritan Johan1548 Brooklyn Lager USA Johan1559 Coors Light USA Johan1670 DAB Tyskland Johan1403 Tuborg Danmark Jonas1403 Tuborg Danmark Juha1403 Tuborg Danmark Juha1522 Karhu Finland Juha1523 Armageddon IPA Nya Zeelan Juha1566 Pilsner Urquell Tjeckien Juha1574 Pabst Blue Ribbo USA Juha1594 Lapin Kulta Finland Juha30023 US Red Ale Finland Juha30658 Stigbergets Sais Sverige Juha11433 Falcon Export Sverige Kalle1519 Saxon Finland Kalle1522 Karhu Finland Kalle1551 Citra Pale Ale Holland Kalle1594 Lapin Kulta Finland Kalle1675 Anchor Steam Bee USA Kalle30023 US Red Ale Finland Kalle11433 Falcon Export Sverige Kjell1515 Punk IPA Storbritan Kjell1548 Brooklyn Lager USA Kjell1559 Coors Light USA Kjell11226 Gigantic IPA USA Lennart11451 Pripps Bla Sverige Lennart11489 Gambrinus Tjeckien Lennart11581 Trashy Blonde Storbritan Lennart1344 Amstel Holland Lennart1403 Tuborg Danmark Lennart1407 Backyard Brew Danmark Lennart1523 Armageddon IPA Nya Zeelan Lennart1540 Westmalle Double Belgien Lennart1565 Bitburger Tyskland Lennart1566 Pilsner Urquell Tjeckien Lennart1574 Pabst Blue Ribbo USA Lennart1594 Lapin Kulta Finland Lennart1642 Fullers London P Storbritan Lennart1650 Orval Belgien Lennart1659 Leffe Blond Belgien Lennart1664 Kwak Belgien Lennart1670 DAB Tyskland Lennart89793 Maredsous Tripel Belgien Lennart1403 Tuborg Danmark Lisen1407 Backyard Brew Danmark Lisen1548 Brooklyn Lager USA Lisen1553 Chang Beer Thailand Lisen1565 Bitburger Tyskland Lisen1594 Lapin Kulta Finland Lisen1657 Chimay vit Belgien Lisen30611 Dugges High Five Sverige Lisen30658 Stigbergets Sais Sverige Lisen11410 Alesmith Decaden USA Magnus1260 Guinness Draught Irland Magnus1407 Backyard Brew Danmark Maria11451 Pripps Bla Sverige Marie11489 Gambrinus Tjeckien Rikard1353 Budvar Tjeckien Rikard1540 Westmalle Double Belgien Rikard1544 Litovel Classic Tjeckien Rikard1611 Breznak Tjeckien Rikard1650 Orval Belgien Rikard1654 Duvel Belgien Rikard1657 Chimay vit Belgien Rikard1659 Leffe Blond Belgien Rikard1664 Kwak Belgien Rikard1670 DAB Tyskland Rikard89793 Maredsous Tripel Belgien Rikard11410 Alesmith Decaden USA Urban1416 Lowenbrau Tyskland Urban1506 Jever Tyskland Urban1565 Bitburger Tyskland Urban1642 Fullers London P Storbritan Urban1670 DAB Tyskland UrbanAppriciate 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 itfrom twhere country = 'USA'group by nameorder by count(*) desclimit 1;If you want all rows when there are ties, then use window functions:
select namefrom (select name, count(*) as cnt, rank() over (partition by country order by count(*) desc) as seqnum from t where country = 'USA' group by name ) twhere seqnum = 1;