Postgresql: Calculate rank by number of true OR clauses

Question

I need to order/rank the results of a PostgreSQL query by the number of OR-clauses that are true. For example, given a query like

SELECT * FROM mytable WHERE cond1 OR cond2 OR cond3 ORDER BY rank DESC

should rank results based on the number of fulfilled conditions. Also approaches that solve this issue with views/stored procedures are very welcome!

score 5 · Accepted Answer · answered Apr 18 '12 at 14:01

5

Repeat the conditions and add them:

SELECT * FROM mytable 
WHERE fld = 'A' OR fldB = CURRENT_DATE OR fldC = 7
ORDER BY
   (fld = 'A')::int + (fldB = CURRENT_DATE)::int + (fldC = 7)::int  
DESC

answered Apr 18 '12 at 14:01

Michael Buen

38,643
9
94
118

score 2 · Answer 2 · edited Apr 19 '12 at 15:10

2

Something like this maybe:

select *
from (
    SELECT * , case when cond1 then 1 else 0 end
             + case when cond2 then 1 else 0 end
             + case when cond3 then 1 else 0 end as cond_count
    FROM mytable 
    WHERE cond1 
       OR cond2 
       OR cond3 
) t
order by cond_count desc

The ugly thing about this solution is that you have every condition twice in the statement, but I can't think of another solution right now.

edited Apr 19 '12 at 15:10

Erwin Brandstetter

605,456
145
1,078
1,228

answered Apr 18 '12 at 14:27

Is it allowed to omit `CASE` keyword? – vyegorov Apr 18 '12 at 14:30
@a_horse_with_no_name: I don't think the user wants to aggregate. Just add up the CASE statements. You might also strip the subselect and put the expression into the `ORDER BY` clause. – Erwin Brandstetter Apr 19 '12 at 15:16

score -2 · Answer 3 · answered Apr 18 '12 at 14:00

The above query will check those conditions from the left side one by one i.e 

if the cond1 is true then 
      return the results order by rank.
if cond1 is false and cond2 is true then
      return the results order by rank.
if cond1 and cond2 both are false but cond3 is true
      returns the results order by rank.
if all conditions are false then no result is returned.     

So in brief it doesn't check all the conditions simultaneously for OR conditions.


Thanks.

Postgresql: Calculate rank by number of true OR clauses

3 Answers3

Linked