# Thread: Best match from the rows

1. ## Best match from the rows

Hi,

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:

tbl.gif

And I give query like :

Code:
`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.

Regards
ZH

2. one way to do it is to assign each criterion a "match" score, then sort by the sum of scores
Code:
```SELECT something
, anything
, 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

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
AS score

This way, if all the conditions are matched - it will score 3. ?

4. Originally Posted by zeeshanhashmi
I can put as much CASE WHEN as possible and it will keep adding to "score" for a particular row ?
yes, that's the idea

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•