Home » SQL & PL/SQL » SQL & PL/SQL » Rownum??
Rownum?? [message #36109] Tue, 06 November 2001 05:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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)

----------------------------------------------------------------------
Previous Topic: What wrong with this code
Next Topic: Re: how to get day of week without using oracle specific keywords
Goto Forum:
  


Current Time: Thu Mar 28 04:12:53 CDT 2024