Mqsql Query to select no of rows from each group

Question

I want to select 3 record from each group by mysql query my table structure is like this -

id | customer | catId
---------------------
 1 | Joe      | 2
 2 | Sally    | 2
 3 | Joe      | 2
 4 | Sally    | 2
 5 | Joe      | 2
 6 | Sally    | 3
 7 | Joe      | 3
 8 | Sally    | 3
 9 | Joe      | 3
10 | Sally    | 4
11 | Joe      | 4
12 | Sally    | 4

I want to select 3 records for each distinct catId means

id | customer | catId
---------------------
 1 | Joe      | 2
 2 | Sally    | 2
 3 | Joe      | 2
 6 | Sally    | 3
 7 | Joe      | 3
 8 | Sally    | 3
10 | Sally    | 4
11 | Joe      | 4
12 | Sally    | 4

I tried this query but its showing only one record for each distinct catId. l

SELECT * FROM table GROUP BY CatIds

and I am getting

id | customer | catId
---------------------
 1 | Joe      | 2
 6 | Sally    | 3
10 | Sally    | 4

Have a look at this answer, it is basically the same thing : http://stackoverflow.com/questions/3140589/mysql-query-select-top-3-rows-from-table-for-each-category — adrien, May 07 '12 at 10:55

score -2 · Answer 1 · answered May 07 '12 at 11:08

-2

You may just run 3 queries instead.

SELECT * FROM table WHERE catId=2 LIMIT 3
SELECT * FROM table WHERE catId=3 LIMIT 3
SELECT * FROM table WHERE catId=4 LIMIT 3

answered May 07 '12 at 11:08

Konstantin Pereiaslov

1,786
1
18
26

I have millions of records I can't write single query for each. – Khoyendra Pande May 07 '12 at 11:14
You can do it from your code. Or see the link suggested by adrien. – Konstantin Pereiaslov May 07 '12 at 11:18
I am not using this query in my code, I just want to use this query in mysql panel. – Khoyendra Pande May 07 '12 at 11:22
What I mean, it's really simple to write a piece of code, that will generate all the queries – Konstantin Pereiaslov May 07 '12 at 11:28

Mqsql Query to select no of rows from each group

1 Answers1