Re: Find next most recent date

On Jan 4, 10:18 am, Chaihana Joe <i..._at_chezburns.com> wrote:
> Good morning.
>
> 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.
>
> I seem to be missing something.

rownum is special. You can read more about it on asktom.oracle.com (search for "rownum=2" for instance).

You'll have to do something like this:

SQL> select * from (select a.*,rownum rn from (select object_id from all_objects order by 1 desc) a where rownum<3) where rn>1   2 ;

 OBJECT_ID RN
---------- ----------

     71310 2

SQL> select * from (select object_id from all_objects order by 1 desc) where rownum<6;

 OBJECT_ID

     71311
     71310
     71307
     71287
     70427

SQL> Received on Fri Jan 04 2008 - 03:59:24 CST