How to Retrieve the Top 5 Menu Items for Each Menu in MySQL?

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!