MySQL operations on grouped fields

MySQL

I have one table t1 like this

a|b|c
-|-|-
1|1|2
1|1|3
1|1|6
1|2|3
1|2|4
1|2|7
1|3|5
2|3|2

I would like a query where the results would be similar to running multiple selects like this:

(SELECT * FROM t1  where a=1 AND b=1  LIMIT 2)
UNION ALL
(SELECT * FROM t1  where a=1 AND b=2  LIMIT 2)

....

(SELECT * FROM t1  where a=1 AND b=n  LIMIT 2)

result: 

a|b|c
1|1|2
1|1|3
1|2|3
1|2|4
1|3|5

How can I accomplish that without knowing how many instances of b there are for a specific a?

Best Answer

In my professional experience, such bizarre queries being required are often indicative of an unnormalized schema (assuming this isn't a random one-off query you need to do). Perhaps you would be better served by giving us your actual table/column names and then letting us offer suggestions on how you can restructure your data.

BUT, just to prove to you that I've been around the block a few times, here is the actual answer to your question:

SET @a = 1, @max_count = 2, @counter = 0;
SELECT a,b,c, count FROM (
   SELECT 
          IF(@counter <= @max_count,
             @counter := @counter + 1,
             @counter := 1) AS count
        , a
        , b
        , c 

     FROM ( SELECT a,b,c 
            FROM t1 
            WHERE a = @a
            ORDER BY a,b,c
          )
       AS t1
)
AS t
HAVING count <= @max_count;

+------+------+------+-------+
| a    | b    | c    | count |
+------+------+------+-------+
|    1 |    1 |    2 |     1 |
|    1 |    1 |    3 |     2 |
|    1 |    2 |    3 |     1 |
|    1 |    2 |    4 |     2 |
|    1 |    3 |    5 |     1 |
+------+------+------+-------+
5 rows in set (0.00 sec)

You must modify the value of @a in the SET statement to the "row group" which you want. You can then ignore the count column that appears in the resultset. Note that the SET statement MUST be run each and EVERY time you want to run the actual query.

Related Question