CODEWITHSUNDEEP
mysql
MagicLee
MagicLee

Reputation: 1

How to find records from MySQL where only part of fields meet the conditions?

I'm a MySQL rookie and I'm stuck on the following problem. I have a table and there're 3 numeric value fields in it. I call them A, B, and C. I want to find records where there're at least two values > 60. One way to solve the problem is

SELEC * FROM table WHERE (A>60 AND B>60) OR (A>60 AND C>60) OR (B>60 AND C>60) OR (A>60 AND B>60 AND C>60)

But I don't think this is an elegant way. There is only 3 values. If there are 10 or 20 value fields in the table, and I also want to find records where there're at least two values > 60. Using the above way to solve the problem would be very ugly. So is there a better way to solve the problem?

Upvotes: 0

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521083

Try using the GREATEST and LEAST functions:

SELECT *
FROM yourTable
WHERE
    GREATEST(A, B, C) > 60 AND                              -- largest value
    (A + B + C) - GREATEST(A, B, C) - LEAST(A, B, C) > 60;  -- 2nd largest value

Demo

But honestly the version you have right now is almost as concise as what I wrote above.

Upvotes: 1

Related Questions