I have a table ‘Players’
In this table the columns are 'ID','surname','nation'
I need a query and result must shows a list of Players by nations limited by 4 for nations
Ex
Table players
ID surn.. nation 1 Garcia spa 2 smith gbr 3 rossi ita 4 villa spa 5 renoir fra 6 muller ger 7 conti ita 8 johnson usa 9 james gbr 10 lopez spa 11 dubois fra 12 petit fra 13 popov rus 14 rodriguez spa 15 weber ger 16 ivanov rus 17 gonzales spa 18 wagner ger 19 bruni ita 20 Smirnov rus 21 white gbr 22 schmidt ger 23 Armstrong usa 24 green gbr 25 Schulz ger
Resultset
Nation| surname- Spa |Garcia- Spa| villa- Spa| lopez- Spa |rodriguez- Gbr |smith- Gbr |james- Gbr| white- Gbr| green - Ita| rossi- Ita |conti- Ita |bruni- Fra |renoir- Fra| dubois- Fra| petit- Ger |muller- Ger| weber- Ger| wagner- Ger |schmidt- Usa |johnson - Usa |armstrong- Rus| popov- Rus |ivanov- Rus |Smirnov -
Advertisement
Answer
With row_number() window function:
select t.nation, t.surname
from (
select *,
row_number() over (partition by nation order by id) rn
from players
) t
where t.rn <= 4
order by t.nation, t.id
See the demo.
Or if your version of SQLite does not support window functions:
select t.nation, t.surname
from (
select p.*,
(select count(*) + 1 from players where nation = p.nation and id < p.id) rn
from players p
) t
where t.rn <= 4
order by t.nation, t.id
See the demo.
Results:
| nation | surname | | ------ | --------- | | fra | renoir | | fra | dubois | | fra | petit | | gbr | smith | | gbr | james | | gbr | white | | gbr | green | | ger | muller | | ger | weber | | ger | wagner | | ger | schmidt | | ita | rossi | | ita | conti | | ita | bruni | | rus | popov | | rus | ivanov | | rus | Smirnov | | spa | Garcia | | spa | villa | | spa | lopez | | spa | rodriguez | | usa | johnson | | usa | Armstrong |