Home > Database > Mysql Tutorial > How to Retrieve the Top 5 Menu Items for Each Menu in MySQL?

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

Patricia Arquette
Release: 2024-11-05 19:44:02
Original
864 people have browsed it

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)
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template