Sql limit record grouped

Question

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 Resultset Answer With row_number() window function: See the demo. Or if your version of SQLite does not support window functions: See the demo. Results:

Accepted Answer

With row_number() window function:select t.nation, t.surnamefrom (  select *,    row_number() over (partition by nation order by id) rn  from players) twhere t.rn <= 4order by t.nation, t.idSee the demo.Or if your version of SQLite does not support window functions:select t.nation, t.surnamefrom (  select p.*,    (select count(*) + 1 from players where nation = p.nation and id < p.id) rn  from players p) twhere t.rn <= 4order by t.nation, t.idSee 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 |

Advertisement

Answer