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.