Re: Find next most recent date

"Chaihana Joe" <ian_at_chezburns.com> wrote in message news:2efafe60-d746-4495-b58f-80569ad79f39_at_q39g2000hsf.googlegroups.com...

> I have a table with a date column. I want to find the next latest date
> in the data in that table.
>
> I have got this far:
>
> SELECT SNAPSHOT_DATE, ROWNUM
> FROM (SELECT DISTINCT SNAPSHOT_DATE FROM MONTHLY_SNAPSHOTS
> ORDER BY SNAPSHOT_DATE DESC ) WHERE ROWNUM > 0
>
> which returns all the dates in the table.
>
> If I change the where clause to WHERE ROWNUM = 1 I just get the most
> recent date.
>
> But if I change the where clause to WHERE ROWNUM = 2 I get an empty
> recordset.
>

rownum = 2 is the number given to the second row you retrieve, but you want the first row you retrieve to have a rownum of 2 - which means you only get a row when the first row IS the second row.

With the appropriate indexing, the optimiser is able to make the following statement efficient:

select max(snapshot_date)
from monthly_snapshot
where

    snapshot_date < (

        select max(snapshot_date)
        from monthly_snapshot

The plan should look like this:

| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  SORT AGGREGATE               |       |
|   2 |   FIRST ROW                   |       |
|*  3 |    INDEX RANGE SCAN (MIN/MAX) | T1_I1 |
|   4 |     SORT AGGREGATE            |       |
|   5 |      INDEX FULL SCAN (MIN/MAX)| T1_I1 |
-----------------------------------------------

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html