The general type of request for any CBD is almost identical. Pick up the recordings by the terms, count the priority, select those with maximalene priority. But getting the highest priority at the same time as all data lines on different CBSs is determined differently. If there are final functions (Postgresql, MS SQL Server, Oracle, SQLite) - so:select *
from (
select m.*,
case when b is null then 0 else 1 end +
case when c is null then 0 else 2 end as prio,
max(case when b is null then 0 else 1 end +
case when c is null then 0 else 2 end) over() as mprio
from mytable m
where a=1 and z=4
and (b=2 or b is null)
and (c=3 or c is null)
) A
where prio=mprio
For MySQL, which does not support window functions, variables may be used: select A.*
from (
select m.*,
case when b is null then 0 else 1 end +
case when c is null then 0 else 2 end as prio,
@mprio:=greatest(@mprio,
case when b is null then 0 else 1 end +
case when c is null then 0 else 2 end) mprio
from mytable m,(select @mprio:=0) B
where a=1 and z=4
and (b=2 or b is null)
and (c=3 or c is null)
) A
where prio=mprio
At the bottom of "Standard" SQL, you can only offer the basis of this request and where prio=select max(case .... ) from mytable ... I mean, in the subpoena, repeat the entire basic request again. Or filter the records on the client.P.S. Prior to publication, the request was checked for all of the listed DSB except SQLite.UPD Option CTE in lieu of final functions:with Q as(
select m.*,
case when b is null then 0 else 1 end +
case when c is null then 0 else 2 end as prio
from mytable m
where a=1 and z=4
and (b=2 or b is null)
and (c=3 or c is null)
)
select * from Q where prio=(select max(prio) from Q)
UPD2 For the CBD, the window support function rank() The request can be shorter:select *
from (
select m.*,
rank() over(order by b, c) as R
from mytable m
where a=1 and z=4 and coalesce(b,2)=2 and coalesce(c,3)=3
) A
where R=1
