Search code examples
mysqllimit

MySQL limit with a where clause

I have some data in a view that I want to search. The data has different types (defined in a column), and I want to define a different limit for each type.

Example my_view

id   |    type    |     content
--        ----          -------
1         'dog'        'Lorem ipsum dolor sit amet, consectetur...'
2         'cat'        'adipisicing elit, sed do eiusmod...'
3         'cat'        'tempor incididunt ut labore...'
4         'dog'        'et dolore magna aliqua...'

I want a query along the lines of:

SELECT * FROM `my_view` WHERE ... LIMIT [[ max of 2 dogs, 1 cat and 1 sheep]]

I could of course do three queries for this and fit them together (either in the query or afterwards), but as far as I am aware, the view will be re-created for each search and so it would be quite inefficient.

Solution

  • You can't do all in one.

    Use Union

    (SELECT * 
FROM my_view
WHERE type='dog'
ORDER BY yyy
LIMIT 2)

UNION

(SELECT * 
FROM my_view
WHERE type='cat'
ORDER BY xxx
LIMIT 1)

    etc.