xenteros xenteros - 2 months ago 7
MySQL Question

Selecting k rows with each value

My table:

NAME SURNAME
John Smith
Jane Smith
Alice Smith
James Bond
James Cameroon
David Beckham





I've got a db and I want to select
k
rows with each
SURNAME
. For
k=1
it's:

SELECT * FROM people GROUP BY SURNAME;


and the output is:

Alice Smith
James Bond
James Cameroon
David Beckham


I can't find a way to query for
k=2
, where I would get (if present) 2 Smiths, 2 Bonds, 2 Cameroons etc or in general: k Smiths, k Bonds, k Cameroons etc. If there are less then k people with this
SURNAME
select all of them. Is it possible in
MySQL
?

Answer

You can use the following query:

SELECT NAME, SURNAME
FROM (
  SELECT NAME, SURNAME,
         @rn := IF(@s = SURNAME, @rn + 1,
                   IF(@s := SURNAME, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @s = '') AS vars
  ORDER BY SURNAME) AS t
WHERE t.rn <= 3

The above query shows an example for k=3. It will select all records from the sample table cited in the OP.

Demo here