Hi,
Assuming a more complicated interpretation of the requirement :
X (here 3) filters with different priorities :
3 : a=null
2 : b=null
1 : c=null
return all and only the rows that match the filter with the highest prio among all rows.
i.e. if there are prio 3 rows return all those
if there are some prio 2, but no prio 3, then return all prio 2 rows,
if there are some prio 1, but no prio 2 or 3, then return all prio 1 rows,
otherwise return no rows
WITH
TestData AS (
select null a, 1 b, 1 c FROM DUAL union all
select null a, 1 b, 2 c FROM DUAL union all
select 2 a, null b, 1 c FROM DUAL union all
select 2 a, null b, 2 c FROM DUAL union all
select 3 a, 1 b, null c FROM DUAL union all
select 3 a, 2 b, null c FROM DUAL),
FilterData AS (
select '3' Prio, a, b, c FROM TestData WHERE a IS NULL union all
select '2' Prio, a, b, c FROM TestData WHERE b IS NULL union all
select '1' Prio, a, b, c FROM TestData WHERE c IS NULL),
PrioData AS (
select Prio, a, b, c, MAX(Prio) OVER (ORDER BY Prio ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MaxPrio
from FilterData)
SELECT *
FROM PrioData
WHERE Prio = MaxPrio
challenge : optimize the SQL so oracle gets as close to a good (best?) full scan algorithm :
1. Start collecting rows for all filters/prios
2. For the first row for prio Y : discard all collected rows for lower prios and stop filtering/collecting them
3. return what remains after scanning the whole table once