Search code examples
sqldatabaseoracle-databaseanalytic-functions

How do I compress this Oracle resultset into values according to row priority, ignoring nulls?


I'll 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

So according to the priority of the row, it overrides previous row values, if given.

I'm attempting to work out a solution using analytical functions over the table, but it just isn't behaving...

I try:

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 the inverse:

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 neither seem to ignore nulls. Any hints?


Solution

  • You need to put rownum = 1 OUTSIDE the analytical 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 results in (using your values above):

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