Retrieving the Top 5 Items from Each Category in MySQL
When working with databases, it often becomes necessary to retrieve data in a specific manner. This particular question involves extracting the top 5 items from each category in MySQL. The tables in question are "menus" and "menuitems," where "menus" contains menu information, and "menuitems" holds details about individual menu items.
The provided query:
SELECT m.profilename, name FROM menus m WHERE (SELECT name from menuitems s where m.menuid = s.menuid limit 5)
Unfortunately, this query is incorrect and will generate the error "Subquery returns more than 1 row." This error occurs because the subquery is returning multiple rows for each menu category, violating the condition that a single menu can only have five items.
To resolve this issue, you will need to use side-effecting variables.
The corrected query:
SELECT profilename, name FROM ( SELECT m.profilename, s.name, @r:=case when @g=m.profilename then @r+1 else 1 end r, @g:=m.profilename FROM (select @g:=null,@r:=0) n cross join menus m left join menuitems s on m.menuid = s.menuid ) X WHERE r <= 5
In this corrected query, we introduce side-effecting variables:
By using these variables, the query will iterate through each menu and its associated items, incrementing the count for each menu. This technique ensures that you retrieve only the top 5 items for each menu category.
The above is the detailed content of How to Retrieve the Top 5 Items from Each Category in MySQL?. For more information, please follow other related articles on the PHP Chinese website!