I posted a solution for one of the posts by a user here :
532492
In the solution, I used the analytic function FIRST_VALUE. Upon experimenting more, I tried using LAST_VALUE, which I thought would give the same result as FIRST_VALUE if you specify the same PARTITION clause and reverse ORDER BY clause. But it dit not. I don't know how to explain this.
with t as
(select 1 as id, 99 as jobid, 'kiran' as owner, to_date('08-02-07','dd-mm-yy') as startdate from dual union all
select 2 as id, 99 as jobid, 'dondapati' as owner, to_date('09-03-07','dd-mm-yy') as startdate from dual union all
select 3 as id, 99 as jobid, 'sunil' as owner, to_date('12-03-07','dd-mm-yy') as startdate from dual union all
select 4 as id, 1000 as jobid, 'ask' as owner, to_date('14-03-07','dd-mm-yy') as startdate from dual union all
select 5 as id, 1000 as jobid, 'borat' as owner, to_date('14-05-07','dd-mm-yy') as startdate from dual
)
select distinct jobid,
first_value(owner) over (partition by jobid order by startdate asc) as first_owner,
first_value(startdate) over (partition by jobid order by startdate asc) as first_start,
first_value(owner) over (partition by jobid order by startdate desc ) as last_owner,
first_value(startdate) over (partition by jobid order by startdate desc) as last_start
from t
order by 1
/
JOBID FIRST_OWN FIRST_STA LAST_OWNE LAST_STAR
---------- --------- --------- --------- ---------
99 kiran 08/FEB/07 sunil 12/MAR/07
1000 ask 14/MAR/07 borat 14/MAY/07
with t as
(select 1 as id, 99 as jobid, 'kiran' as owner, to_date('08-02-07','dd-mm-yy') as startdate from dual union all
select 2 as id, 99 as jobid, 'dondapati' as owner, to_date('09-03-07','dd-mm-yy') as startdate from dual union all
select 3 as id, 99 as jobid, 'sunil' as owner, to_date('12-03-07','dd-mm-yy') as startdate from dual union all
select 4 as id, 1000 as jobid, 'ask' as owner, to_date('14-03-07','dd-mm-yy') as startdate from dual union all
select 5 as id, 1000 as jobid, 'borat' as owner, to_date('14-05-07','dd-mm-yy') as startdate from dual
)
select distinct jobid,
last_value(owner) over (partition by jobid order by startdate desc) as first_owner,
last_value(startdate) over (partition by jobid order by startdate desc) as first_start,
last_value(owner) over (partition by jobid order by startdate asc ) as last_owner,
last_value(startdate) over (partition by jobid order by startdate asc) as last_start
from t
order by 1
JOBID FIRST_OWN FIRST_STA LAST_OWNE LAST_STAR
---------- --------- --------- --------- ---------
99 dondapati 09/MAR/07 dondapati 09/MAR/07
99 kiran 08/FEB/07 kiran 08/FEB/07
99 sunil 12/MAR/07 sunil 12/MAR/07
1000 ask 14/MAR/07 ask 14/MAR/07
1000 borat 14/MAY/07 borat 14/MAY/07
I was expecting the same output for both, but didn't get any. Am I using the analytic function incorrectly or am I just confused?
<br>Note : I'm running on 10g release 10.2.0.2.0</br>