I am using said function in my query
SQL> select job, max(hiredate) keep (dense_rank last order by hiredate) Hdate from emp
2 group by job
3 ;
JOB HDATE
--------- ---------
ANALYST 19-APR-87
CLERK 23-MAY-87
MANAGER 09-JUN-81
PRESIDENT 17-NOV-81
SALESMAN 28-SEP-81
Can I use here Dense_rank second last () here ?
I I want to fetch second last record instead of Last record what should I do?
kindly help.
OR
I have another Query
SQL> select job,hiredate,rn from (
2 select job, hiredate, dense_rank() Over (order by hiredate) Rn
3 from emp)
4 where rn <= 7
5 /
JOB HIREDATE RN
--------- --------- ----------
CLERK 17-DEC-80 1
SALESMAN 20-FEB-81 2
SALESMAN 22-FEB-81 3
MANAGER 02-APR-81 4
MANAGER 01-MAY-81 5
MANAGER 09-JUN-81 6
SALESMAN 08-SEP-81 7
7 rows selected.
Is this possible that I can calculate Last Two Date Difference of Job wise.
Like Salesman has 2 records hiredate 20-feb-81 and 22-feb-81 I want to calculate last 2 date difference.
other example
Manager Has 3 records Last 2 dates are 01-May-81 and 09-Jun-81 so calculate the difference.
Edited by: 867210 on Jul 6, 2011 4:06 AM