Clever Geek Handbook

Sql - order by number of conditions fulfilled

Suppose I have a query with multiple conditions that are in an OR relationship like this:

SELECT * FROM TABLE WHERE (c1) OR (c2) OR (c3) OR ...

I don't want the results to be ordered based on the number of conditions they satisfied. This means that records that satisfy c1 and c2 and c3 showed first (3 conditions), then records that satisfy c1 and c2, or c1 and c3, or c2 and c3 (2 conditions), and at the end of the record that satisfy c1 or c2 or c3

+3


source to share


1 answer


Make an expression that looks like this:

( CASE WHEN c1 THEN 1 ELSE 0 END
+ CASE WHEN c2 THEN 1 ELSE 0 END
+ CASE WHEN c3 THEN 1 ELSE 0 END)


It will give you the number of conditions the current record is satisfied with.

+5


source






More articles:


All Articles