Home MYSQL select top 5 of a count from table that is grouped by user
Reply: 0

MYSQL select top 5 of a count from table that is grouped by user

user1934
1#
user1934 Published in May 23, 2018, 7:18 am

I apologize in advance as I may not be describing my problem properly. I am trying to write a query that takes the top 5 most popular chosen_user_items per user and concatenating the top rows per user into a comma separated string via group_concat and then grouping by user_id's.

For example, if user_id of 1 has five rows for item_id of 1, two rows for item_id of 2, three rows for item_id of 3, and a single row for 4 5 and 6, then the result would be 1, 3, 2, 4, 5.

Here is my example table structure.

Name: chosen_user_items

id | user_id | item_id
------------------------
1  | 1       | 1
2  | 1       | 4
3  | 1       | 19
4  | 1       | 10
5  | 1       | 13
.  | 1       | 1
.  | 1       | 11
.  | 1       | 18
.  | 1       | 212
.  | 1       | 654
.  | 2       | 1
.  | 2       | 28
.  | 2       | 568
.  | 2       | 112
.  | 2       | 354
.  | 3       | 4
.  | 3       | 4
.  | 3       | 19
.  | 3       | 212
.  | 3       | 654
.  | 3       | 4
.  | 3       | 4
.  | 3       | 253
.  | 3       | 187
.  | 3       | 212

And here is an example of my desired output:

user_id | group_concat_results
------------------------------
1       | 1, 4, 19, 13, 212
2       | 1, 28, 568, 212, 354
3       | 4, 212, 19, 654, 253

Here is the query that I have so far

SELECT `chosen_user_items`.`item_id`, COUNT(`chosen_user_items`.`item_id`) AS 'item_count' 
FROM `chosen_user_items` 
WHERE `chosen_user_items`.`user_id` = 1
GROUP BY `chosen_user_items`.`item_id` 
ORDER BY `item_count` DESC 
LIMIT 5

While this works great for a single user, I want to be able to run this query just once for all users (in order to avoid doing hundreds or thousands of database queries), and having to manually concat the results in a language such as PHP.

Thanks in advance.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.410385 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO