We help IT Professionals succeed at work.

Second largest date

john_t
john_t asked
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

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:
How about this:

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.

EXPERTS: Post a comment if you think somebody deserves credit here!
==========
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
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
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.