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

Question

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?

score 2 · Accepted Answer · edited Nov 04 '08 at 15:11

2

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

edited Nov 04 '08 at 15:11

TheSoftwareJedi

34,421
21
109
151

answered Nov 04 '08 at 14:14

ScottCher

14,651
6
26
25

See an additional discussion I started regarding the nature of ordering with analytics: http://stackoverflow.com/questions/262278/oracle-analytics-partitions-and-ordering-of-sql-queries. While your edits works in this case, you should still order within the partition clause. – ScottCher Nov 04 '08 at 17:41
I disagree about ordering in the partition clause, though I 'd like to see the plan. My results are already ordered, and I'm taking advantage of documented functionality. I wouldn't want it to reorder it 3 times. – TheSoftwareJedi Nov 04 '08 at 21:49

score -1 · Answer 2 · answered Nov 04 '08 at 13:47

-1

The COALESCE function may be of help to you here. Perhaps like ...

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

answered Nov 04 '08 at 13:47

Alan

3,815
1
26
35

wouldn't that be replacing nulls with 0's? Not what I'm looking for at all! – TheSoftwareJedi Nov 04 '08 at 13:49
Settle down, Beavis. I wrote that it MAY be of help to you... just an idea. You make a fine point about the zeros. – Alan Nov 05 '08 at 13:20

score -1 · Answer 3 · answered Nov 04 '08 at 18:20

An alternative:

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 performance of this may be different from the analytic version; whether it is better or worse depends on your data and environment.

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

3 Answers3

Linked