I have 6 columns in Postgres table:

A1 char[]
A2 char[]
A3 int REFERENCES ... -- FK 
B1 char[]
B2 char[]
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
.                 A1 and B2 OR A2 and B1
.                 A1 and B3 OR A3 and B1
.                 A2 and B3 OR A3 and B2
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.

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.

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.

I have 6 columns in postgresql

A1
A2
A3
 
B1
B2
B3

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

I know postgresql doesn't care about order of WHERE clause and I must prepare ORDER clause or find different approach.

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

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

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

A1, A2, B1, B2 are arrays(char), A3 and B3 are FK (int)

and 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.

I have 6 columns in Postgres table:

A1 char[]
A2 char[]
A3 int REFERENCES ... -- FK 
B1 char[]
B2 char[]
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
.                 A1 and B2 OR A2 and B1
.                 A1 and B3 OR A3 and B1
.                 A2 and B3 OR A3 and B2
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.

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.

I have 6 columns in postgresql

A1
A2
A3

B1
B2
B3

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

I know postgresql doesn't care about order of WHERE clause and I must prepare ORDER clause or find different approach.

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

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

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

I have 6 columns in postgresql

A1
A2
A3

B1
B2
B3

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

I know postgresql doesn't care about order of WHERE clause and I must prepare ORDER clause or find different approach.

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

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

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

A1, A2, B1, B2 are arrays(char), A3 and B3 are FK (int)

and 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.