Geek asks and answers

Mqsql query to select the number of rows from each group

I want to select 3 records from each group at the request of mysql. My table structure is as follows:

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 entries for each individual catId element

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 only showed one entry for each individual catId. l

SELECT * FROM tableGROUP BY CatIds

and i get

id | customer | catId
---------------------
 1 | Joe      | 2
 6 | Sally    | 3
10 | Sally    | 4
+3
source share
1 answer

Instead, you can simply run 3 queries.

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

More articles:


All Articles