Selecting k rows with each value

Question

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?

If `k=2` and there is only `1` `Bond`, should this value be selected? — Giorgos Betsos, Sep 12 '16 at 12:51

Giorgos Betsos · Accepted Answer · 2016-09-12T13:05:49.397

1

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

edited Sep 12 '16 at 13:05

answered Sep 12 '16 at 12:58

Giorgos Betsos

71,379
9
63
98

That's art! Thanks! – xenteros Sep 12 '16 at 13:31

score 0 · Answer 2 · answered Sep 12 '16 at 12:52

0

SELECT Name,Surname,Count(SurName) AS k FROM people 
GROUP BY SURNAME
HAVING k>=2

answered Sep 12 '16 at 12:52

apomene

14,282
9
46
72

It only adds a column to the result. It's a wrong answer :/ – xenteros Sep 12 '16 at 12:54

Shota Papiashvili · Answer 3 · 2016-09-13T09:53:07.780

-1

You can can distinct for the surname and join it with the same tables but limited to your k. this way you will get for each surname k rows

SELECT DISTINCT(`surname`)
    LEFT JOIN (
            SELECT `name` FROM `people` p2 where p2.surname = p1.surname limit 2
        ) x
FROM `people` p1

edited Sep 13 '16 at 09:53

answered Sep 12 '16 at 12:52

Shota Papiashvili

690
5
14

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this". – abarisone Sep 13 '16 at 09:07

Selecting k rows with each value

3 Answers3