We help IT Professionals succeed at work.

# Second largest date

on
I don't know how to find a second largest date from a date column - say CREATE_DATE. My column also contains time-stamp.

any help?
thanks
Comment
Watch Question

## View Solution Only

Commented:
You will see, example 3 will be the fastest way.

EXAMPLE 1
---------
DECLARE
vbDUMMY BOOLEAN := FALSE;
BEGIN
FOR i IN (SELECT create_date FROM [table]
ORDER BY create_date DESC)
LOOP
IF vbDUMMY THEN
DBMS_OUTPUT.PUT_LINE('second largest date: '||TO_CHAR(i.create_date,'DD.MM.YYYY HH24:MI:SS'));
EXIT;
END IF;
vbDUMMY := TRUE;
END LOOP;
END;
/

EXAMPLE 2
---------
SET PAGESIZE 0
SELECT 'second largest date: '||TO_CHAR(MAX(create_date),'DD.MM.YYYY HH24:MI:SS')
FROM [table]
WHERE create_date < (SELECT MAX(create_date)
FROM [table])
/

EXAMPLE 3
---------
DECLARE
vdDUMMY date;
BEGIN
SELECT MAX(create_date)
INTO vdDUMMY
FROM [table]
WHERE create_date < (SELECT MAX(creaet_date)
FROM [table]);
DBMS_OUTPUT.PUT_LINE('second largest date: '||TO_CHAR(vdDummy,'DD.MM.YYYY HH24:MI:SS'));
END;
/

Hope that's what you looked for

chdba

Commented:
On 8i, I'd

select distinct created_date
from (select created_date,
rank() over (order by created_date desc) as date_rank
from x
)
where date_rank = 2;

or else

select created_date from x x1
where 1 =
(select count(distinct created_date)
from x x2
where x2.created_date > x1.created_date);

Commented:
Actually, the analytic function needs amendment. The following runs quickly on a fair sized table

select pay_end_dt
from (select pay_end_dt, rank() over (order by pay_end_dt desc) as date_rank
from (select distinct pay_end_dt from ps_pay_tax)
)
where date_rank = 2;

Commented:
this might work :-

select max(create_date)
from   table_1
where  create_date <
( select max(create_date)
from   table_1 )
/

Performance might suffer if the table's very large.

Commented:
Or try this:

select mycolumn
from mytable
where rownum < (select max(rownum) from mytable  )
minus select  mycolumn from mytable
where rownum < (select max(rownum) - 1 from mytable)
order by my column desc
/

Commented:
SELECT CREATE_DATE FROM (SELECT ROWNUM DATE_NUMBER, CREATE_DATE FROM TABLE_NAME ORDER BY CREATE_DATE DESC) WHERE DATE_NUMBER = 2

But in this case you get Full Scan of table

Commented:

select max(CREATE_DATE)
from some_table
where CREATE_DATE < (select max(CREATE_DATE) from some_table)

Commented:
Dear John,

I can think of the following two methods you can use to determine the 2nd highest date in your table:

Method 1
========

SELECT MAX(create_date)
FROM table
WHERE create_date != (SELECT MAX(create_date)
FROM table
) ;

Method 2
========
SELECT create_date
FROM table a
WHERE (SELECT COUNT(create_date)
FROM table b
WHERE b.create_date > a.create_date
) = 1 ;

Similarly, you can you Method 2 above to determine the third highest date as follows:

SELECT create_date
FROM table a
WHERE (SELECT COUNT(create_date)
FROM table b
WHERE b.create_date > a.create_date
) = 2;

Regards,
Sudip.
BRONZE EXPERT
Author of the Year 2009

Commented:
Hi john_t,
It appears that you have forgotten this question.  Please move toward finalizing it.

If sudipdg's answer did not help you, you may reject that answer.   If another Expert's comment was helpful, you can then select a comment as an answer.   Or you can delete this question to get a refund of your points.

==========
DanRollins -- EE database cleanup volunteer

Commented:
Dear: sudipdg

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Grade A for chdba to be given