CODEWITHSUNDEEP
mysqlcountlimitwhere-clause
Cesar
Cesar

Reputation: 534

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)

Upvotes: 1

Views: 534

Answers (3)

Petah
Petah

Reputation: 46060

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You don't use LIMIT in a single query. One method uses variables:

select b.*
from (select b.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as rn
      from books b cross join
           (select @c := -1, @rn := 0) params
      where category in (1, 2, 3)
      order by category
     ) b
where rn <= 3;

Upvotes: 0

1000111
1000111

Reputation: 13519

Along with MySQL user defined variables you can achieve this:

SELECT 
t.*
FROM 
(
    SELECT 
    *,
    IF( @sameCategory = category , @rn := @rn + 1,
         IF(@sameCategory := category, @rn := 1, @rn := 1)
    ) AS rowNumber
    FROM books
    CROSS JOIN (SELECT @sameCategory := 0, @rn := 1) var
    ORDER BY category 
) AS t
WHERE t.rowNumber <= 3
ORDER BY t.category

Upvotes: 0

Related Questions