No doubt everybody has solved it before, but ten seconds on Google didn’t show any obvious results, so here we go: How to pick the top 5 records of each group.
\nWe’ll define “top 5” as “the first 5 records when ordering by a field called `rating`”.<\/p>\n
For MySQL 8.0 and later, use the RANK() window function inside either a common table expression or a derived table:<\/p>\n
WITH cte AS (\nSELECT *, RANK() OVER(PARTITION BY groupId ORDER BY rating) AS rank\nFROM theTable\n)\nSELECT * FROM cte WHERE rank <= 5;<\/pre>\n5.7 and earlier didn’t have CTEs. Subqueries will suffice.<\/p>\n
SELECT \n theTable.* \nFROM \n theTable \n JOIN (\n SELECT \n t1.groupId, \n t1.rating, \n COUNT(t2.groupId) AS theCount \n FROM theTable t1 \n LEFT JOIN theTable t2 ON t1.groupId = t2.groupId AND t1.rating > t2.rating \n GROUP BY t1.groupId, t1.rating \n HAVING theCount < 5\n ) AS dt USING (groupId, rating);<\/pre>\nPre-4.1, you couldn’t even use subqueries.<\/p>\n
SELECT @oldGroup := 0, @count := 0;\n\nSELECT \n @oldGroup := groupId,\n theTable.*\nFROM theTable \nHAVING (@count := IF(groupId <=> @oldGroup, @count+1, 0)) < 5\nORDER BY groupId, rating;<\/pre>\n","protected":false},"excerpt":{"rendered":"No doubt everybody has solved it before, but ten seconds on Google didn’t show any obvious results, so here we go: How to pick the top 5 records of each group.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-mysql-faq"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-A","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=36"}],"version-history":[{"count":2,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":636,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions\/636"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}