SQL order results by number of fields matched.

Question

Bit of a complicated SQL question here.

I currently have a SELECT statement which matches several fields, like this.

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 modify the statement so that it uses OR's instead of AND's so that it selects all records which match any of the fields.

The next step is to rank the results using a 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 field2 and field 3 match then the score would be 1400

Match 2 - If field1 and field 4 match then the score would be 1010

Match 1 would be at the top of the results.

Any help with some SQL to achieve this would 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

Use the CASE expression to create the score:

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

And then you can order by the score (and see it, if that's relevant). Alternatively, just ORDER BY the complex expression, as in other answers.

Answer 3

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 4

If you need to do it all in one Select, then it will have to be something horrible like:

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: you can also put the multiple case section into the SELECT as an output field if you want the score to be output. You can then order by it by quoting its alias or its column index (as Yannick M. does below)

A slightly nicer solution might be to break it up into multiple queries, with the first one selecting the basic data into a temp table, and then subsequent queries scanning over the temp table to adjust the score values. But depending on your environment, that might not be an option.

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 a single aggregate query- if that answers your question. The code would 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 score for a particular factor, you could subquery that like so:

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