Johny Pie Johny Pie - 6 months ago 10
MySQL Question

Mysql select rows with limit to different column

The structure of table goes as bellow

Posts (
id int,
category_id int,
popularity int,
)


I wanna select 5 categories each having 5 rows.

Like Select * from posts where category_id in ("1" , "2" , "3" , "4" , "5") limit 5 -- limit each category_id by selection of 5

Answer

Use UNION to connect multiple select statements to show one combined result.
It is probably the easiest way to solve your problem.

Like this:

SELECT * FROM posts where category_id=1 limit 5
UNION 
SELECT * FROM posts where category_id=2 limit 5
UNION 
SELECT * FROM posts where category_id=3 limit 5
UNION 
SELECT * FROM posts where category_id=4 limit 5
UNION 
SELECT * FROM posts where category_id=5 limit 5

The same question has already been asked: MySQL: Limiting number of results received based on a column value | Combining queries It provides a more sophisticated solution.

Comments