How to compress this Oracle result set into values ​​according to line priority, ignoring zeros?

Question

I will simplify the problem as much as possible:

I have an oracle table:

row_priority, col1, col2, col3
0, .1, 100, {null}
12, {null}, {null}, 3
24, .2, {null}, {null}

Desired Result:

col1, col2, col3
.2, 100, 3

Thus, in accordance with the priority of the line, it redefines the values ​​of previous lines, if they are specified.

I am trying to work out a solution using analytic functions on a table, but it just doesn't behave ...

I'm trying to:

select last_value(col1 ignore nulls) over () col1,
       last_value(col2 ignore nulls) over () col2,
       last_value(col3 ignore nulls) over () col3
from (select * from THE_TABLE order by row_priority)
where rownum = 1

or vice versa:

select first_value(col1 ignore nulls) over () col1,
       first_value(col2 ignore nulls) over () col2,
       first_value(col3 ignore nulls) over () col3
from (select * from THE_TABLE order by row_priority desc)
where rownum = 1

And nothing ignores zeros. Any clues?

Answer 1

You need to put rownum = 1 OUTSIDE analytic query

SELECT  *
FROM    (   select          last_value(col1 ignore nulls) over () col1,
                            last_value(col2 ignore nulls) over () col2,
                            last_value(col3 ignore nulls) over () col3
            from (select * from THE_TABLE ORDER BY ROW_PRIORITY)
        )
WHERE   ROWNUM = 1

which leads to (using your values ​​above):

COL1   COL2    COL3
------ ------- ----
0.2    100     3

Answer 2

The COALESCE function can help you here. Perhaps how ...

select first_value(coalesce(col1,0) ignore nulls) over () col1,
       first_value(coalesce(col2,0) ignore nulls) over () col2,
       first_value(coalesce(col3,0) ignore nulls) over () col3
from THE_TABLE

Answer 3

:

SELECT
  MAX(col1) KEEP (DENSE_RANK LAST ORDER BY row_priority),
  MAX(col2) KEEP (DENSE_RANK LAST ORDER BY row_priority),
  MAX(col3) KEEP (DENSE_RANK LAST ORDER BY row_priority)
FROM the_table

The effectiveness of this may differ from the analytical version; whether it is better or worse depends on your data and the environment.