MySQL return 2 rows per result

Question

Until today i thought i know something about MySQL. OK lets say we have one table like this:

id | some_name |some_number
-----------------------------
1  | test      | 33
2  | test      | 34
3  | test      | 35
3  | test2     | 36
3  | test2     | 37

and i want to write query to return something like this:

test 33
test 34
test2 36
test2 37
test3 12
test3 34
.
.
.

and so on. I want to return only 2 result per same name. It is easy to use limit and return only one result per name but I'm stuck to return multiple results per same name in this case 2 but might be an n results. Work around is to make some script that will do:

select some_name, some_number from tbl_name limit 2;

and to repeat it for every distinct some_name i have in table. Is there any elegant solution for MySQL? I would be grateful if you share that with me.

look at how a user variable is defined in this prior solution. I believe it will achieve what you're after. http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — xQbert, Sep 02 '15 at 16:45

score 1 · Answer 1 · answered Sep 02 '15 at 16:50

You can use a user variable to add a counter of each row of a name, then just select the rows where the counter is less than or equal to 2 (untested):-

SELECT some_name, some_number
FROM
(
    SELECT some_name, some_number, @cnt=(@some_name = some_name, @cnt + 1, 1) AS cnt, @some_name:=some_name
    FROM
    (
        SELECT some_name, some_number 
        FROM tbl_name 
        ORDER BY some_name, some_number
    ) sub0
    CROSS JOIN 
    (
        SELECT @some_name:='', @cnt:=0
    )
    sub1
) sub2
WHERE cnt <= 2

score 1 · Answer 2 · answered Sep 02 '15 at 19:48

1

you could try this,

select some_name,some_number from yourTable  t 
where 
(select count(*) from yourTable 
where 
some_number<=t.some_number and some_name=t.some_name)<3

answered Sep 02 '15 at 19:48

nazark

1,240
2
10
15

score 0 · Answer 3 · answered Sep 02 '15 at 20:53

This partially solved my problem:

set @num := 0, @name := '';

select distinct number, name
from (
  select number, name,
     @num := if(@name = name, @num +1, 1) as row_number,
      @name := name as dummy
     from karian2

     ) as x where x.row_number <= 2;

It fails some time to return 2 results because sub query is not returning distinct values. Example here: http://sqlfiddle.com/#!2/952ca/23

MySQL return 2 rows per result

3 Answers3