How can I LIMIT on various WHERE clauses in MySQL?

Question

Let's say I have the following MySQL table:

id | species
------------
1  | dog
2  | dog
3  | dog
4  | cat
5  | cat
6  | fish
7  | fish
8  | fish

I want to get the ids of 2 dogs, 1 cat, and 1 fish (they don't have to be in order). For example, {1, 2, 4, 6} would be a valid output.

Is there a way to do this in one SQL query? As far as I know, LIMIT is a fairly simple operator that can't do this. Maybe WHERE can be manipulated? Or would multiple SQL queries be the best way of doing this?

Answer 1

As far as I know the best you can do is to use UNION to combine the results of three SELECTs, e.g.:

SELECT id, species FROM animals
  WHERE species = 'dog'
  LIMIT 2
UNION
SELECT id, species FROM animals
  WHERE species = 'cat'
  LIMIT 1
UNION
SELECT id, species FROM animals
  WHERE species = 'fish'
  LIMIT 1;

# Producing:
#
# id | species
# ---+--------
#  1 | dog
#  2 | dog
#  4 | cat
#  6 | fish