Menu
I want to be able to return 5 menuitem per menu. I've tried this several script but had no luck. here are the tables
menus
-------
menuid int()
profileName varchar(35)
menuitems
-----------
itemid int()
name varchar(40)
Here what I have now. I'm getting the error message with the script below. Error: Subquery returns more then 1 row.
SELECT m.profilename, name
FROM menus m
WHERE (SELECT name
from menuitems s
where m.menuid = s.menuid
limit 5)
Any suggestion is great appreciated.
935
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement. Here is the alternate query to select first 10 elements.
METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
1 Answer. ORDER BY id ASC; In the above query, we used subquery with the TOP clause that returns the table with the last 5 records sorted by ID in descending order. Again, we used to order by clause to sort the result-set of the subquery in ascending order by the ID column.
You have to use side effecting variables for this
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
