How to find second max date?

[DevangP]

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

[pando]

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

[Mnemonical]

for example:
Table = Emp
Column = Hire_Date (Date Type)

SELECT MAX(Hire_Date - 1) FROM Emp;



F.

[pando]

eh? what if the second row is 2 days?

[Mnemonical]

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.

[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.

[ales]

If you have Oracle without analytic function:

* to find second max date:

select max(hiredate) from emp
where hiredate<(select max(hiredate) from emp)

* to find second min date:

select min(hiredate) from emp
where hiredate>(select min(hiredate) from emp)

[DevangP]

First of all thanx..do u have any doc on dense_rank and over. I dont understand how that works?

Thanx..

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.

[ales]

do u have any doc on dense_rank and over

Here