Mr Miyagi
Reputation: 31
How to select all rows that have the max count and not only the first row?
The aim is to select all the names of the persons who have drank all beers from tyskland. In this case, im looking to find the name of the person that has max(count) of beers from germany. I formed smaller tables along the way to try to get the result: From the table below :
table qwe
sysnr beer land namn
--------------- ------------------------------ --------------- ---------------
1260 Guinness Draught Irland Agneta
11226 Gigantic IPA USA Alan
11410 Alesmith Decadence USA Alan
11581 Trashy Blonde Storbritanien Alan
1260 Guinness Draught Irland Alan
1403 Tuborg Danmark Alan
1416 Lowenbrau Tyskland Alan
1506 Jever Tyskland Alan
1515 Punk IPA Storbritanien Alan
1523 Armageddon IPA Nya Zeeland 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
1566 Pilsner Urquell Tjeckien Alan
1574 Pabst Blue Ribbon Export USA Alan
1585 San Miguel Spanien Alan
1594 Lapin Kulta Finland Alan
1625 Sierra Nevada Pale Ale USA Alan
1642 Fullers London Pride Storbritanien Alan
1649 Samuel Adams Boston Ale 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
1675 Anchor Steam Beer USA Alan
89607 Lagunitas IPA USA Alan
89793 Maredsous Tripel Belgien Alan
11410 Alesmith Decadence USA Dick
1553 Chang Beer Thailand Dick
1642 Fullers London Pride Storbritanien Dick
1222 Sofiero Sverige Dina
1574 Pabst Blue Ribbon Export 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 Decadence USA Janne
1260 Guinness Draught Irland Janne
1506 Jever Tyskland Janne
1559 Coors Light USA Janne
1649 Samuel Adams Boston Ale USA Janne
11410 Alesmith Decadence USA Johan
1515 Punk IPA Storbritanien Johan
1548 Brooklyn Lager USA Johan
1559 Coors Light USA Johan
1670 DAB Tyskland Johan
1403 Tuborg Danmark Jonas
1403 Tuborg Danmark Juha
1522 Karhu Finland Juha
1523 Armageddon IPA Nya Zeeland Juha
1566 Pilsner Urquell Tjeckien Juha
1574 Pabst Blue Ribbon Export USA Juha
1594 Lapin Kulta Finland Juha
30023 US Red Ale Finland Juha
30658 Stigbergets Saison 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 Beer USA Kalle
30023 US Red Ale Finland Kalle
11433 Falcon Export Sverige Kjell
1515 Punk IPA Storbritanien 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 Storbritanien Lennart
1344 Amstel Holland Lennart
1403 Tuborg Danmark Lennart
1407 Backyard Brew Danmark Lennart
1523 Armageddon IPA Nya Zeeland Lennart
1540 Westmalle Double Belgien Lennart
1565 Bitburger Tyskland Lennart
1566 Pilsner Urquell Tjeckien Lennart
1574 Pabst Blue Ribbon Export USA Lennart
1594 Lapin Kulta Finland Lennart
1642 Fullers London Pride Storbritanien 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 Saison Sverige Lisen
11410 Alesmith Decadence 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 Decadence USA Urban
1416 Lowenbrau Tyskland Urban
1506 Jever Tyskland Urban
1565 Bitburger Tyskland Urban
1642 Fullers London Pride Storbritanien Urban
1670 DAB Tyskland Urban
I did:
create table zzz as select namn, count(land) as nrofbeerfromtyskland from qwe where land = 'Tyskland' group by namn;
namn nrofbeerfromtyskland
-------------------------------------------------- --------------------------------------------------
Alan 4
Janne 1
Johan 1
Lennart 2
Lisen 1
Rikard 1
Urban 4
create view xxx as select namn, max(nrofbeerfromtyskland) from zzz;
namn max(nrofbeerfromtyskland)
-------------------------------------------------- --------------------------------------------------
Alan 4
The result should be:
Alan
Urban
How does one not make it to only show Alan in this case?
Upvotes: 0
Views: 16
Answers (1)
forpas
Reputation: 164064
You can do it with RANK() window function:
select t.namn
from (
select namn, rank() over (order by count(*) desc) rn
from qwe
where land = 'Tyskland'
group by namn
) t
where t.rn = 1
See the demo.
This query returns all the persons who drank the most beers from 'Tyskland'.
If you want only the persons who drank all the beers from 'Tyskland' then use this:
select namn
from qwe
where land = 'Tyskland'
group by namn
having count(distinct beer) = (select count(distinct beer) from qwe where land = 'Tyskland')
See the demo.
Results:
| namn |
| ----- |
| Alan |
| Urban |
Upvotes: 1
Related Questions
- select max count in SQL
- Select all rows with the MAX value in SQLite?
- Getting rows that has max value
- How do I select rows with maximum value?
- SQlite select top N rows from the table for each item
- SQL - only display rows that have the max value
- Select most recent rows that match a condition in SQLite
- How to MAX(COUNT(x)) in SQLite
- How only select max row in sqlite?
- SQLite Max of Count problem