Hi,
Can any one help me in finding the second latest date from the table. i.e. in emp table I've 3 rows.
1. 1st jan, 2002
2. 2nd Jan, 2002
3. 3rd Jan, 2002.
I want to get the second record. Plz. its urgent...
thanx
Devang
Printable View
Hi,
Can any one help me in finding the second latest date from the table. i.e. in emp table I've 3 rows.
1. 1st jan, 2002
2. 2nd Jan, 2002
3. 3rd Jan, 2002.
I want to get the second record. Plz. its urgent...
thanx
Devang
select * from
(select a.*, rank() over(order by hiredate) as rank_date
from emp a)
where rank_date=2
it's probably semi correct, you probably have to look at dense_rank function, I forgot now since I dont have the docs handy
for example:
Table = Emp
Column = Hire_Date (Date Type)
SELECT MAX(Hire_Date - 1) FROM Emp;
F.
eh? what if the second row is 2 days?
And:
VARIABLE getsecond varchar2(8)
DECLARE
v_Max date;
v_Second date;
BEGIN
SELECT MAX(HIRE_DATE) INTO v_Max from FF;
SELECT MAX(HIRE_DATE) into v_Second FROM FF WHERE HIRE_DATE != v_Max;
:getsecond := to_char(v_second, 'dd/mm/yy');
END;
I understood (or misunderstood) he wanted the second
last date, so if there are two or more second last dates,
so the date is only one.
03/02/02
02/02/02
02/02/02
01/02/02
the second last date is 02/02/02.
F.
to get the second min. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
to get the second max. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate desc) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
hth.
If you have Oracle without analytic function:
* to find second max date:* to find second min date:Code:select max(hiredate) from emp
where hiredate<(select max(hiredate) from emp)
Code:select min(hiredate) from emp
where hiredate>(select min(hiredate) from emp)
First of all thanx..do u have any doc on dense_rank and over. I dont understand how that works?
Thanx..
Quote:
Originally posted by balajiyes
to get the second min. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
to get the second max. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate desc) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
hth.
HereQuote:
do u have any doc on dense_rank and over