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

Returning the Top 5 Items from Each Category in MySQL

Challenge:
Retrieving the top 5 menu items from each category in a database consisting of two tables, "menus" and "menuitems."

Solution:

The presented code aims to display the top 5 items per menu category. However, it returns with an error due to the subquery retrieving multiple rows. To address this, side effecting variables can be utilized to track the position of each row within the category.

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

Breakdown:

1. The outer query selects the پروفایلname (category) and name (menu item) from a subquery (X) that assigns row position (r) and group identifier (g) values through side effecting variables (@r, @g).
2. The subquery employs a cross join between the menus table (m) and a table (n) initialized with @g and @r as NULL and 0, respectively.
3. The JOIN with the menuitems table (s) adds the menu item names based on the menu IDs.
4. The CASE statement assigns row position values, incrementing by 1 for each row within the same category. If a new category is encountered, it resets the counter to 1.
5. The WHERE clause filters the results to include only rows within the top 5 for each category, based on the row position values.

The above is the detailed content of How to Retrieve the Top 5 Menu Items from Each Category in MySQL?. For more information, please follow other related articles on the PHP Chinese website!