Returning the Top 5 Menu Items in Each Category Using MySQL
In a MySQL database, you have the 'menus' table with fields 'menuid' and 'profileName,' and the 'menuitems' table with fields 'itemid,' 'name,' and 'menuid.' You want to retrieve the top five menu items for each menu in the 'menus' table.
One approach you tried resulted in an error:
SELECT m.profilename, name FROM menus m WHERE (SELECT name from menuitems s where m.menuid = s.menuid limit 5)
However, the error indicates that the subquery is returning more than one row. To overcome this, you can use side-effecting variables. Here's a revised solution:
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 query, the side-effecting variables @g and @r are used to keep track of the current menu name (profileName) and the position (row number) within that menu. The CASE statement ensures that the position starts at 1 for each new menu and increments for subsequent menu items. The final WHERE clause filters for the top 5 rows with the lowest position numbers.
The above is the detailed content of How to Retrieve the Top 5 Menu Items for Each Menu in MySQL?. For more information, please follow other related articles on the PHP Chinese website!