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 -
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 |