How to Retrieve the Top 5 Items from Each Category in MySQL?

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:

• @g: keeps track of the current menu name
• @r: counts the items for each menu
• r <= 5: limits the result to the top 5 items per menu

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!