How to force order of WHERE clauses in PostgreSQL?

Question

I have 6 columns in Postgres table:

A1 character varying(5)[]
A2 character varying(5)[]
A3 int REFERENCES ... -- FK 
B1 character varying(5)[]
B2 character varying(5)[]
B3 int REFERENCES ... -- FK 

I need a SELECT where the first row matched is the winner (limit 1) with matching to A group and B group.

I know Postgres doesn't care about the order of WHERE clauses and I must prepare an ORDER clause or find a different approach.

I want to prepare lookup with priority of matching, my importance of WHERE clauses is as follow:

highest priority: (A1 and B1) OR
.                 (A1 and B2 OR A2 and B1) OR
.                 (A1 and B3 OR A3 and B1) OR
.                 (A2 and B3 OR A3 and B2) OR
lowest priority:  (A3 and B3)

The query is matching with 6 values, like:
a1 to A1, a2 to A2, a3 to A3, b1 to B1, b2 to B2, b3 to B3

So A1 and B1 means a1 matched with A1 and b1 matched with B1,

in SQL this part is written as:

("A1" @> ARRAY['19956']::varchar(5)[] AND "B1" @> ARRAY['27407']::varchar(5)[])

with a1='19956', b1='27407'.

Is it possible to prepare it as single query, despite declarative aspect of SQL? I am considering 5 joins on the same table, but maybe there is an easier way.

Answer 1

This information is crucial:

the first row matched is the winner (limit 1)

You do not actually need any sort order for a single result. ORDER BY is just one idea how to solve the task. I suggest a completely different approach, probably (much) faster:

SELECT * FROM tbl WHERE A1 AND B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B2 OR A2 and B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B3 OR A3 and B1
UNION ALL
SELECT * FROM tbl WHERE A2 AND B3 OR A3 and B2
UNION ALL
SELECT * FROM tbl WHERE A3 AND B3
LIMIT 1;

This is assuming each SELECT can only return a single row. Else you get an arbitrary pick or you need to add ORDER BY to individual SELECTs enclosed in parentheses for a deterministic pick. (See linked answers below.)

The beauty of it: Postgres stops evaluating as soon as the first row is found. Test with EXPLAIN ANALYZE to see "never executed" for trailing SELECTs. So you can use indexes, which might mean orders of magnitude in performance.

And you can conveniently return a default row if nothing matches. Just append one more SELECT before LIMIT 1.

BTW, this is a single query.

Related:

• Optimize a query on two big tables
• Select first record if none match

Answer 2

If they are not numeric values and you are just checking for something that is true or not then you can use a case statement in your where to give them sortable values:

order by 
    case when (A1 and B1) then 1
    when (A1 and B2 OR A2 and B1) then 2
    when (A1 and B3 OR A3 and B1) then 3
    when (A2 and B3 OR A3 and B2) then 4
    when (A3 and B3) then 5
    else 6
    end asc

Answer 3

ORDER BY 
  A1 and B1 DESC,
  A1 and B2 OR A2 and B1 DESC,
  A1 and B3 OR A3 and B1 DESC,
  A2 and B3 OR A3 and B2 DESC,
  A3 and B3 DESC;