SELECT 3 records per user group by on two columns

Question

I've been stuck in a complex MySQL query. Here is my table:

+--------------------------------------+
| id | user_id | category_id | post_id |
+--------------------------------------+
| 1  | 23      | 5           | 213     |
| 2  | 23      | 5           | 214     |
| 3  | 23      | 5           | 215     |
| 4  | 23      | 5           | 216     |
| 5  | 23      | 6           | 217     |
| 6  | 23      | 6           | 218     |
| 7  | 23      | 6           | 219     |
| 8  | 23      | 6           | 220     |
| 9  | 55      | 13          | 221     |
| 10 | 55      | 13          | 222     |
| 11 | 55      | 16          | 223     |
| 12 | 55      | 16          | 234     |
| 13 | 55      | 22          | 235     |
| 14 | 55      | 22          | 256     |
| 15 | 55      | 22          | 261     |
| 16 | 62      | 13          | 272     |
| 17 | 62      | 13          | 273     |
| 18 | 62      | 24          | 277     |
| 19 | 62      | 24          | 278     |
| 20 | 62      | 24          | 288     |
| 21 | 62      | 31          | 289     |
| 22 | 62      | 31          | 290     |
+--------------------------------------+

Now what I wish is for each user_id I want 2 rows of data but each row should have a different category_id, like the below resultset:

+--------------------------------------+
| id | user_id | category_id | post_id |
+--------------------------------------+
| 1  | 23      | 5           | 213     |
| 5  | 23      | 6           | 217     |
| 9  | 55      | 13          | 221     |
| 11 | 55      | 16          | 223     |
| 16 | 62      | 13          | 272     |
| 18 | 62      | 24          | 277     |
+--------------------------------------+

The query I've used so far using GROUP BY clause only manages to return a single row for each group, but I want 2 or possibly 3. Here is my query:

SELECT * FROM (
    SELECT id, user_id, category_id, post_id 
    FROM my_table
    GROUP BY user_id, category_id) 
AS sub GROUP BY sub.user_id;

Please suggest how to go from here...

Related: http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — Aziz Shaikh, Jun 10 '14 at 11:20

M Khalid Junaid · Accepted Answer · 2014-06-10T19:57:23.270

2

You can use user-defined variables to give rank for the same user group and in outer query you can simple use your condition for showing 2 categories per user or 3 or more as you need to

SELECT id,
  user_id,
  category_id,
  post_id,
  rank FROM 
(SELECT tt.* ,
@rank:= CASE WHEN @group = user_id THEN @rank + 1 ELSE 1 END rank,
@group:= tt.user_id
FROM
(SELECT 
  id,
  user_id,
  category_id,
  post_id 
FROM
  table_name 
GROUP BY user_id,
  category_id
  ORDER BY user_id,
  category_id 
  ) tt
 JOIN (SELECT @rank:=0,@group:=0) t1
 ) new_t
 WHERE rank <=2 /* will give 2 records per user change to 3 if you need to show 3 records per user */

Demo 2 Rows Per User

Demo 3 Rows Per User

edited Jun 10 '14 at 19:57

answered Jun 10 '14 at 11:41

M Khalid Junaid

63,861
10
90
118

Thanks it worked perfectly. Though I have another need. Now I have an extra view_count field in above table. So how can I query the 3 records of each user having minimum view count. What I am trying to accomplish is show the posts as featured which have minimum view count for each user. Do you have any idea regarding that? – khalid Jun 11 '14 at 06:47
@khalid it would b better if you ask another question with all the information and sample code or query so other peoples can see your problem if you want me to look in to your new question then post the link back here – M Khalid Junaid Jun 11 '14 at 09:52
Hi @m-khalid-junaid Here is my new question - hope you can help: http://stackoverflow.com/questions/24179265/select-3-records-per-user-group-by-on-two-columns-according-to-view-count – khalid Jun 12 '14 at 08:00

SELECT 3 records per user group by on two columns

1 Answers1

Demo 2 Rows Per User

Demo 3 Rows Per User

Linked