Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
I don't quite understand what you're looking for. Please post an example of what your query output should be. –  Michael Jan 25 '12 at 23:08
    
I had a mistake, edited it –  tasaras Jan 25 '12 at 23:14
    
Does t1 have a Primary Key? –  ypercube Jan 25 '12 at 23:29
    
@ypercube no it doesn't –  tasaras Jan 25 '12 at 23:31

3 Answers 3

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.

share|improve this answer
    
So basically we're iterating through the result set after we ordered it. When we count 2 in a group, we skip every other in the group. This is actually MUCH easier to do in your programming language of choice by looping over the query result (make sure you order the result set). The logic is the same. –  Brian Papantonio Jan 26 '12 at 5:52
    
+1 but oh for a ROW_NUMBER fucntion in MySQL... –  gbn Jan 26 '12 at 8:06
    
@Brian: You can replace the SET with a subquery: CROSS JOIN (SELECT @a := 1, @max_count := 2, @counter := 0) AS dummy –  ypercube Jan 26 '12 at 12:37

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
share|improve this answer
    
It looks like some sort of Join table to me... might explain the duplicate rows. –  Kyle Macey Jan 26 '12 at 0:48
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
share|improve this answer
    
what about the limit? –  tasaras Jan 25 '12 at 23:39
    
it's set to limit 1 in the group by –  Kyle Macey Jan 25 '12 at 23:41
    
1 is just an example, in reality I would need a limit of example 100. what I basically need is to get for a specific a (ex. a=1) all 100 first b=1 then all 100 first b=2 and so on, until I reach b=max for that specific a. thanks –  tasaras Jan 26 '12 at 3:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.