Zerenity
Reputation: 107
How to select the highest number of occurances for a certain text value?
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
Upvotes: 0
Views: 44
Answers (1)
Gordon Linoff
Reputation: 1270713
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;
Upvotes: 1
Related Questions
- Writing SQL query for getting maximum occurrence of a value in a column
- How to select all rows that have the max count and not only the first row?
- select max count in SQL
- Highest Number of Occurrences without using Limit
- How to retrieve the most frequent value of a column for a specific ID in a table
- Finding the Max of multiple elements in the same column
- How do I select rows with maximum value?
- sql query to get for each item the line of a group that has the maximum occurence
- SQLite How to find the most common occurrences of a value
- Get the highest-n values including duplicates in SQL