# How to find second max date?

• 01-22-2002, 02:02 PM
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
• 01-22-2002, 03:50 PM
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
• 01-22-2002, 04:40 PM
Mnemonical

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

SELECT MAX(Hire_Date - 1) FROM Emp;

F.

• 01-22-2002, 05:15 PM
pando
eh? what if the second row is 2 days?
• 01-22-2002, 07:21 PM
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.
• 01-23-2002, 12:31 AM
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.
• 01-23-2002, 02:44 AM
ales
If you have Oracle without analytic function:

* to find second max date:
Code:

```select max(hiredate) from emp where hiredate<(select max(hiredate) from emp)```
* to find second min date:
Code:

```select min(hiredate) from emp where hiredate>(select min(hiredate) from emp)```
• 01-28-2002, 01:22 PM
DevangP
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.

• 01-29-2002, 02:01 AM
ales
Quote:

do u have any doc on dense_rank and over
Here