How to return rows that have the same column values in MySql

by

This is an example of a “sets-within-sets” query. I recommend aggregation with the having clause, because it is the most flexible approach.

select score
from t
group by score
having sum(id = 2) > 0 and -- has id = 2
       sum(id = 4) > 0     -- has id = 4

What this is doing is aggregating by score. Then the first part of the having clause (sum(id = 2)) is counting up how many “2”s there are per score. The second is counting up how many “4”s. Only scores that have at a “2” and “4” are returned.

More Related Contents:

  1. How to filter SQL results in a has-many-through relation
  2. Select values that meet different conditions on different rows?
  3. Which DB technology is sued to solve the following search issue? [closed]
  4. MySQL pivot row into dynamic number of columns
  5. How to join two tables mysql?
  6. UPDATE multiple rows with different values in one query in MySQL
  7. Multiple Updates in MySQL
  8. SQL split comma separated row [duplicate]
  9. Storing JSON in database vs. having a new column for each key
  10. MySQL – Subtracting value from previous row, group by
  11. How do I restore a dump file from mysqldump?
  12. MySQL – Conditional Foreign Key Constraints
  13. How to count items in comma separated list MySQL
  14. Groupwise maximum
  15. Filter by COUNT(*)?
  16. Error Code: 1406. Data too long for column – MySQL
  17. MySQL order by “best match”
  18. How to select rows that have current day’s timestamp?
  19. MySQL concatenation operator
  20. Checking a table for time overlap?
  21. Does mysql have the equivalent of Oracle’s “analytic functions”?
  22. MySql – Way to update portion of a string?
  23. MySQL INNER JOIN select only one row from second table
  24. Storing IPv6 Addresses in MySQL
  25. Insert/ Update random date in MySQL
  26. Replace null with 0 in MySQL
  27. Get total hours worked in a day mysql
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment