Rownum?? [message #36109] |
Tue, 06 November 2001 05:54 |
homer
Messages: 17 Registered: October 2001
|
Junior Member |
|
|
I want to delete the 10 latest records of my table:
delete my_table where rownum > (select (max(rownum)-10)
from my_table);
It doesn't work.
Why not?
----------------------------------------------------------------------
|
|
|
Re: Rownum?? [message #36112 is a reply to message #36109] |
Tue, 06 November 2001 06:24 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
It doesn't work because there is no correlation between rownum and the "latest" rows. The definition of "latest" is arbitrary and has to be defined by you - usually by storing a datetime in an audit column.
As a pseudo-column, rownum is simply not applicable in this context.
Let us know if you want an example using a datetime value instead.
----------------------------------------------------------------------
|
|
|
Re: Rownum?? [message #36241 is a reply to message #36109] |
Mon, 12 November 2001 21:00 |
wijnand engelkes
Messages: 2 Registered: November 2001
|
Junior Member |
|
|
to find 10 highest rownums
(***NOT*** necessarily the 10 latest records)
try:
select rownum,value from whatever_table
group by rownum,value
having rownum > (select (max(rownum) - 11) from whatever_table)
----------------------------------------------------------------------
|
|
|