How to find second max date?

# Thread: How to find second max date?

#### Hybrid View

1. Registered User
Join Date
Sep 2001
Posts
52
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

2. Pando & Company
Join Date
Jun 2000
Location
Madrid, Spain
Posts
7,447
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

3. Senior Member
Join Date
Dec 2001
Location
Brazil
Posts
282

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

SELECT MAX(Hire_Date - 1) FROM Emp;

F.

4. Pando & Company
Join Date
Jun 2000
Location
Madrid, Spain
Posts
7,447
eh? what if the second row is 2 days?

5. Senior Member
Join Date
Dec 2001
Location
Brazil
Posts
282
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.

6. Advisor
Join Date
Mar 2001
Location
Reading, U.K
Posts
598
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.

7. Advisor
Join Date
Apr 2001
Location
Czechia
Posts
712
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)```

8. Registered User
Join Date
Sep 2001
Posts
52
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.

9. Advisor
Join Date
Apr 2001
Location
Czechia
Posts
712
do u have any doc on dense_rank and over
Here

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Click Here to Expand Forum to Full Width