zeeshanhashmi — 2014-01-12T04:41:42-05:00 — #1
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.
Please help !
r937 — 2014-01-12T07:37:25-05:00 — #2
one way to do it is to assign each criterion a "match" score, then sort by the sum of scores
, CASE WHEN age < 25 THEN 2 ELSE 0 END +
CASE WHEN marks > 190 THEN 1 ELSE 0 END AS score
WHERE age < 25
OR marks > 190
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
zeeshanhashmi — 2014-01-12T23:38:58-05:00 — #3
Thank you Rudy !
That would be the solution - thanks once again.
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
This way, if all the conditions are matched - it will score 3. ?
r937 — 2014-01-13T03:04:55-05:00 — #4
yes, that's the idea
zeeshanhashmi — 2014-01-13T03:54:46-05:00 — #5
Thanks a lot.
I do not have as much SQL knowledge as you have, but last night I was on bed and thinking of something that can do this scoring thing.