SQL order results by number of matching fields

Question

A bit complicated SQL question here.

I currently have a SELECT statement that matches multiple fields, for example:

SELECT field1, field2, field3, field4, field5
FROM table
WHERE field1 = 'variable 1'  
AND field2 = 'variable 2' 
AND field3 = 'variable 3' 
AND field4 = 'variable 4' 
AND field5 = 'variable 5' 

I would like to change the statement so that it uses OR instead of AND, so that it selects all records that match any of the fields.

The next step is to rank the results using the scoring system.

If field 1 was matched then 1000 is added to the score
If field 2 was matched then 800 is added to the score
If field 3 was matched then 600 is added to the score
If field 4 was matched then 10 is added to the score
If field 5 was matched then 1 is added to the score

So...

Match 1 - If field 2 and field 3 match, the score will be 1400

Match 2 - If field 1 and field 4 match, the score will be 1010

Match 1 will be at the top of the results.

Any help with some SQL to achieve this will really be appreciated.

Answer 1

TRY:

SELECT
    ....
    FROM ....

    ORDER BY
        (CASE WHEN field1 = 'variable 1' THEN 1000 ELSE 0 END
        +CASE WHEN field2 = 'variable 2' THEN 800 ELSE 0 END
        +CASE WHEN field3 = 'variable 3' THEN 600 ELSE 0 END
        +CASE WHEN field4 = 'variable 4' THEN 10 ELSE 0 END
        +CASE WHEN field5 = 'variable 5' THEN 1 ELSE 0 END
        ) DESC

Answer 2

Select, - , :

SELECT field1, field2, field3, field4, field5
FROM table
WHERE field1 = 'variable 1'  
 OR field2 = 'variable 2' 
 OR field3 = 'variable 3' 
 OR field4 = 'variable 4' 
 OR field5 = 'variable 5'
ORDER BY (Case when field1 = 'variable 1' then 1000 else 0 end
        + Case when field2 = 'variable 2' then 800 else 0 end
        + Case when field3 = 'variable 3' then 600 else 0 end
        + Case when field4 = 'variable 4' then 10 else 0 end
        + Case when field5 = 'variable 5' then 1 else 0 end) DESC

edit: SELECT , , . , ( .)

, temp, temp, . .

Answer 3

CASE :

CASE WHEN field1 = 'variable 1' THEN 1000 ELSE 0 END +
CASE WHEN field2 = 'variable 2' THEN  800 ELSE 0 END +
...
AS score

( , ). , ORDER BY , .

Answer 4

How about this:

SELECT field1, field2, field3, field4, field5
FROM table
WHERE field1 = 'variable 1'  
OR field2 = 'variable 2' 
OR field3 = 'variable 3' 
OR field4 = 'variable 4' 
OR field5 = 'variable 5'
ORDER BY
  CASE WHEN field1 = 'variable 1' THEN 1000 ELSE 0 END +
  CASE WHEN field2 = 'variable 2' THEN 800 ELSE 0 END +
  CASE WHEN field3 = 'variable 3' THEN 600 ELSE 0 END +
  CASE WHEN field4 = 'variable 4' THEN 10 ELSE 0 END +
  CASE WHEN field5 = 'variable 5' THEN 1 ELSE 0 END
DESC

Answer 5

SELECT field1, field2, field3, field4, field5,
  (CASE WHEN field1 = 'variable 1' THEN 1000 ELSE 0 END +
  CASE WHEN field2 = 'variable 2' THEN 800 ELSE 0 END +
  CASE WHEN field3 = 'variable 3' THEN 600 ELSE 0 END +
  CASE WHEN field4 = 'variable 4' THEN 10 ELSE 0 END +
  CASE WHEN field5 = 'variable 5' THEN 1 ELSE 0 END) as score
FROM table
ORDER BY 6 DESC;


+------+------+------+------+------+-------+
| f1   | f2   | f3   | f4   | f5   | score |
+------+------+------+------+------+-------+
|    1 |    2 | NULL | NULL | NULL |  1800 |
| NULL |    2 | NULL | NULL | NULL |   800 |
| NULL | NULL |    3 | NULL |    5 |   601 |
+------+------+------+------+------+-------+
3 rows in set (0.00 sec)

Answer 6

In my opinion, the best way to do this is in one consolidated request - if that answers your question. The code will look something like this:

SELECT
    SUM(CASE WHEN FIELD1 = 'VARIABLE 1' THEN 1000 ELSE 0 END) F1FIND
,   SUM(CASE WHEN FIELD2 = 'VARIABLE 2' THEN 800 ELSE 0 END) F2FIND
,   SUM(CASE WHEN FIELD3 = 'VARIABLE 3' THEN 600 ELSE 0 END) F3FIND
,   SUM(CASE WHEN FIELD4 = 'VARIABLE 4' THEN 10 ELSE 0 END) F4FIND
,   SUM(CASE WHEN FIELD5 = 'VARIABLE 5' THEN 1 ELSE 0 END) F5FIND
FROM
    TABLE
;

And if you just want to return a single estimate for a specific factor, you can ask for something like this:

SELECT
    F1FIND+F2FIND+F3FIND+F4FIND+F5FIND AS TOTAL_FIND
FROM
    ([ABOVE QUERY])
WHERE
    FACTOR = 'SOMETHING'
;