I need a way to get the best match of some conditions in WHERE clause.
The WHERE clause has about 8 different conditions and from the rows, I want to get the results GROUPED BY only those which are BEST MATCH for the given condition.
For example, look at the table below:
And I give query like :
SELECT * from users WHERE age < 25 OR marks > 190
and this SHOULD show:
Row: 1 and 2 as the ages are < 25 and marks are > 190. However it MUST not show Row 4, because YES the age is < 25 but marks is NOT > 190.
I know that I can do it by using “AND” instead of “OR” but in my table there are about 30 columns and I must use “OR” to get the results. Only thing is that I want the BEST POSSIBLE ROWs that matches most of the conditions in the where clause.
one way to do it is to assign each criterion a “match” score, then sort by the sum of scores
SELECT something
, anything
, just_not_the_dreaded_evil_select_star
, CASE WHEN age < 25 THEN 2 ELSE 0 END +
CASE WHEN marks > 190 THEN 1 ELSE 0 END AS score
FROM users
WHERE age < 25
OR marks > 190
ORDER
BY score DESC
in the example above, i’ve used a higher score for age meeting its criterion than for marks meeting its criterion
whether you discard the rows not having a high enough score using sql or in your application program is up to you
I can put as much CASE WHEN as possible and it will keep adding to “score” for a particular row ? Can I give 1 point for each matched condition ? like :
CASE WHEN age < 25 THEN 1 ELSE 0 END +
CASE WHEN marks > 190 THEN 1 ELSE 0 END +
CASE WHEN blah = blah THEN 1 ELSE 0 END
AS score
This way, if all the conditions are matched - it will score 3. ?
