Cesar Cesar - 3 months ago 12
MySQL Question

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)

Answer

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

Comments