MySQL operations on grouped fields

Question

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?

Answer 1

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.

Answer 2

This may work (if you have no duplicate rows):

SELECT t1.*
FROM 
      t1 
  JOIN
      ( SELECT DISTINCT b
        FROM t1
        WHERE a = 1
      ) AS td
    ON t1.c >= COALESCE(
       ( SELECT t1.c
         FROM t1 AS ti
         WHERE ti.b = td.b
           AND ti.a = 1
         ORDER BY c DESC
         LIMIT 1 OFFSET 99
       ) , -2000000000 )
WHERE t1.a = 1

Answer 3

SELECT t1.* #Desired Output
  FROM t1, t2  
  WHERE t1.b >= t2.num #where the b value is less than or equal to the number count 
  AND t2.a = "1" #The selected input value
  GROUP BY t1.b; #return results grouped under a single 'b' entry