1

Right now I have a query that looks something like this

SELECT id 
FROM products 
WHERE products.details->'make' = ? 
    OR products.details->'model' = ? 
    OR products.details->'year' = ?

I'd like to sort the results by how many of the where statements matched, so for example if the three terms that were used in the query were ford, f150, 1985, and I got two results. One that had all three terms in it (a ford f150 from 1985), and a second result that had only two matches (a ford f150 from 1991).

Is there a way for me to do this with postgreSQL 9.3, or will I need to use my application layer to figure out how many matches there were?

1 Answer 1

2

One way:

SELECT id 
FROM   products 
WHERE  details->'make' = ? 
    OR details->'model' = ? 
    OR details->'year' = ?
ORDER  BY CASE WHEN details->'make' = ?  THEN 0 ELSE 1 END
        + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END
        + CASE WHEN details->'year' = ?  THEN 0 ELSE 1 END;

It's a bit simpler to invert the logic:
NULL and FALSE results increase the value (and decrease the ranking).

2
  • Instead of inverting the logic would I be able to use ASC or DESC at the end of the ORDER BY to display from highest val to lowest val or visca-versa? Sep 24, 2014 at 20:59
  • 1
    @jacobsoloid: Yes. Switch 0 and 1 and append DESC to get the same result. Or just switch DESC and ASC to invert the sort order. Sep 24, 2014 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.