5

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.

6
+400

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:

  • no need adding default value here, no rows is a valuable as well and can be easily handled when operating on the result (a programmer's point of view) – Sławomir Lenart Aug 25 '17 at 8:53
  • Adding a final SELECT to return a row with default(s) is totally optional. If defaults come from another query, it can save another round trip to the DB server, though. – Erwin Brandstetter Aug 27 '17 at 1:53
3

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
-1
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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