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.

On Jan 4, 10:18 am, Chaihana Joe <i... (AT) chezburns (DOT) com> wrote:
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>