oracle-databaseanalytic-functions

Oracle Analytics - partitions and ordering of sql queries

This came up when answering another user's question (TheSoftwareJedi)...

Given the following table:

ROW_PRIORITY   COL1     COL2    COL3
0              0.1      100     <NULL>
12             <NULL>   <NULL>  3
24             0.2      <NULL>  <NULL>

and the following query:

select  'B' METRIC, ROW_PRIORITY,
        last_value(col1 ignore nulls) over (ORDER BY ROW_PRIORITY) col1,
        last_value(col2 ignore nulls) over (ORDER BY ROW_PRIORITY) col2,
        last_value(col3 ignore nulls) over (ORDER BY ROW_PRIORITY) col3
from    (SELECT * FROM ZTEST);

I get these results:

METRIC  ROW_PRIORITY    COL1    COL2    COL3
B            0          0.1     100     <NULL>
B           12          0.1     100     3
B           24          0.2     100     3

EXPECTED:

METRIC  ROW_PRIORITY    COL1    COL2    COL3
B            0          0.2     100     3
B           12          0.2     100     3
B           24          0.2     100     3

The question is of course, why don't I get 0.2 for each row priority in col1, etc? LAST_VALUE is supposed to perform the ORDER BY first and then choose the last value from the partition. In the case of the query above, the partition is the entire recordset so I would to see my expected results above.

Can anyone explain?

Solution

  • when you include an ORDER by in the Partitioning clause, you can include a windowing clause to be explicit.

    If you want these LAST_VALUES to be over all rows, you should include this after your Order By:

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    That should fix your query.

    More details from the docs:

    If you do not specify a ROW or a RANGE clause, the window size is determined as follows: