Menu
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?
809
MySQL allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses. What is Operator? An operator is a special keyword used to join or change clauses within a WHERE clause.
The MySQL LIMIT ClauseThe LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
