0

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

Johny Pie
  • 843
  • 3
  • 14
  • 37

1 Answers1

1

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.

Community
  • 1
  • 1
iLikeMySql
  • 736
  • 3
  • 7
  • is that an efficient way to do when dealing with thousands of rows. Table was for example – Johny Pie May 30 '16 at 12:04
  • No, With so many rows, you could write a stored procedure that creates a table for you with the desired output.The Procedure could still consist of "simple" SQL. If you want to solve it with one query, have a look at the question i linked in my answer – iLikeMySql May 30 '16 at 13:33