Hello,
Is it possible to obtain multiple columns from an analytic function with a window?
I have a table with 4 columns, a pid, a test_id, a date, and the result of a test. I'm using an analytic function to obtain, for each row, the current test value, and the maximum test value in the next 2 days like so:
create table test_table ( pid int, test_id int, test_date date, res int );
insert into test_table values ( 1, 1, to_date( '01-jan-2004'), 10 );
insert into test_table values ( 1, 1, to_date( '02-jan-2004'), 100 );
insert into test_table values ( 1, 1, to_date( '03-jan-2004'), 1000 );
insert into test_table values ( 1, 1, to_date( '04-jan-2004'), 10000 );
select
pid,
test_id,
test_date,
res,
MAX ( res ) over (
partition BY pid, test_id
order by test_date
RANGE BETWEEN CURRENT ROW AND INTERVAL '2' DAY FOLLOWING
) AS max_result_next_two_day
from test_table;
This is working fine, but I would like to also obtain the date when the max result occurs? It does not particularly matter which of the 'dates when max occurs' is returned, but the first one ordered by test date would be preferred.
I can see that this would be possible using a self join, but I'd like to know if there is a better way? I cannot use the FIRST_VALUE analytic function and order by result, because the window function needs to be ordered by the date.
It would be a great help if you could provide any pointers/suggestions.
Thanks,
Dan
ops$tkyte%ORA10GR2> select pid, test_id, test_date, res,
2 to_number(substr(data,1,50)) max_res,
3 to_date(substr(data,51),'yyyymmddhh24miss') max_date
4 from ( select pid, test_id, test_date, res,
5 MAX ( rpad( nvl( to_char( res ), ' ' ), 50 ) || to_char(test_date,'yyyymmddhh24miss'))
6 over ( partition BY pid, test_id order by test_date
7 RANGE BETWEEN CURRENT ROW AND INTERVAL '2' DAY FOLLOWING) AS data
8 from test_table)
9 /
PID TEST_ID TEST_DATE RES MAX_RES MAX_DATE
---------- ---------- --------- ---------- ---------- ---------
1 1 01-JAN-04 10 1000 03-JAN-04
1 1 02-JAN-04 100 10000 04-JAN-04
1 1 03-JAN-04 1000 10000 04-JAN-04
1 1 04-JAN-04 10000 10000 04-JAN-04
is my typical approach to this. You need to beware of null values (your first N-1 values you concatenate must be nvl'ed to make sure they "appear" in the data). You need to use a predicable width for each of the first n-1 fields (hence my use of rpad). You need to encode the data in a way that "sorts" (eg: my example would not work for negative values of RES necessarily)