Search code examples
mysqlcountlimitwhere-clause

How to make a partial LIMIT in a SQL Select

I know that with a LIMIT at the end of a SQL statement, like this:

 SELECT * FROM books WHERE 1 LIMIT 3

it will show at most 3 items from table books.

Now imagine that we have a field, category and filter by it:

 SELECT * FROM books WHERE category IN (1, 3, 5) LIMIT 3

I will get those rows with category with values 1 OR 3 OR 5, and again a limit of rows of 3.

How can I get a limit of 3 for each category? (outline of basic idea)

 SELECT * FROM books WHERE category IN (1, 3, 5) LIMITS (3, 3, 3)
Solution

  • You could do this with a UNION:

    (SELECT * FROM books WHERE category = 1 LIMIT 3)
UNION 
(SELECT * FROM books WHERE category = 3 LIMIT 3)
UNION 
(SELECT * FROM books WHERE category = 5 LIMIT 3)

    http://dev.mysql.com/doc/refman/5.7/en/union.html