Re: Find next most recent date
Date: Fri, 4 Jan 2008 01:59:24 -0800 (PST)
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
> 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 ;
SQL> select * from (select object_id from all_objects order by 1 desc) where rownum<6;
71311 71310 71307 71287 70427
SQL> Received on Fri Jan 04 2008 - 03:59:24 CST