Home » Developer & Programmer » Forms » delete last n rows of a table
delete last n rows of a table [message #77695] Mon, 05 November 2001 16:05 Go to next message
satish
Messages: 112
Registered: September 2000
Senior Member
hi,
how do i delete last n rows of a table?

i should be prompted with the Number of Rows to be deleted form table x:
and then should delete those many rows from the table x.

Please let me know asap.

thanks
satish

----------------------------------------------------------------------
Re: delete last n rows of a table [message #77701 is a reply to message #77695] Mon, 05 November 2001 22:10 Go to previous messageGo to next message
Enno Oost
Messages: 12
Registered: October 2001
Junior Member
Hi Satish,

I guess, that the records of your table are ordered by a column ( e.g. sort_col NUMBER ).
Then you can delet the "last" n records using
DELETE FROM table WHERE sort_column > ( ( SELECT MAX( sort_column ) FROM table ) - n )

I think this will work.
Ciao ... Enno

----------------------------------------------------------------------
Re: delete last n rows of a table [message #77703 is a reply to message #77695] Tue, 06 November 2001 01:18 Go to previous messageGo to next message
Bhanudas Panse
Messages: 1
Registered: November 2001
Junior Member
Hi Satish

I tried this solution. There is one error. And that is position of n.

n should be subtracted from max(sort_column),
and not outside the sub-query statement.
Putting it outside generates an error.

Correct sql statement would be
DELETE FROM table
WHERE sort_column > ((SELECT MAX(sort_column )-n FROM table ))

You can have & prefixed with n, so that you will be prompted for the value of n.

----------------------------------------------------------------------
Re: delete last n rows of a table [message #77707 is a reply to message #77703] Tue, 06 November 2001 03:53 Go to previous messageGo to next message
Enno Oost
Messages: 12
Registered: October 2001
Junior Member
Uuuups ! Sorry, my fault !
Bhanudas is right, of cause.

Ciao ... Enno

----------------------------------------------------------------------
icon5.gif  Re: delete last n rows of a table [message #288589 is a reply to message #77707] Tue, 18 December 2007 02:20 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
But,

This query only deletes last rows when you do order by of the columns.

But what I wanted to know is without selecting the max & then deciding the last rows,
how can one delete the last n records in a table, regardless of the ordering.

Thanks and Regards,
Prachi
Re: delete last n rows of a table [message #288594 is a reply to message #288589] Tue, 18 December 2007 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you do not order them, how do you know which records are "last"? It is a relational table and there's no way to determine order or records unless you specify it using the ORDER BY clause.

If you are talking about records which were last entered into the table, you don't know that either unless there's such an information stored somewhere in the database (for example, record contains a timestamp or a sequence). Then you'd be able to delete records created within the last hour (just as an example).
Re: delete last n rows of a table [message #288930 is a reply to message #288594] Wed, 19 December 2007 01:32 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
You said :-

If you do not order them, how do you know which records are "last"? It is a relational table and there's no way to determine order or records unless you specify it using the ORDER BY clause.


Ok, when you want the lat rows from the sorted data, then this
" DELETE FROM table
WHERE sort_column > ((SELECT MAX(sort_column )- &n FROM table ))"

query is ok.

Now, you said :-

If you are talking about records which were last entered into the table, you don't know that either unless there's such an information stored somewhere in the database (for example, record contains a timestamp or a sequence). Then you'd be able to delete records created within the last hour (just as an example).


Yes I want the last records inserted, (I mean when you use SELECT statement for a table without using order by clause, then the last n records).

Now see this :

1> Select Top N Rows from a Table :-

select * from (select * from temp_emp) where rownum <=4;

Here N = 4

2> Delete top N records

delete from (select * from temp_emp) where rownum <=4;

Here N = 4

Here we can do a said above.

Even see the below :-

3> Select last N records?
select * from (select * from ptemp order by rownum desc)where rownum<=3;

Then why can not we delete last N records?

I tried it with using Select last N records?,
but it did not work.

Thanks and regards,
Prachi
Re: delete last n rows of a table [message #288970 is a reply to message #288930] Wed, 19 December 2007 03:04 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

2> Delete top N records

delete from (select * from temp_emp) where rownum <=4;

This query will delete 4 RANDOMLY chosen records from the 'temp_emp' table.

Quote:

3> Select last N records?

select * from (select * from ptemp order by rownum desc)where rownum<=3;

This will return records ordered by ROWNUM which contains a number indicating the order in which the row was retrieved. In other words, the same query might return different result unless you use ORDER BY some table column.

A ROWNUM value is not assigned permanently to a row (this is a common misconception)!

Please, read this OraFAQ Forum topic which discusses the ORDER BY issue. Read it carefully and you'll see what I meant by saying that only ORDER BY guarantees that records will be returned in desired order.

Also, check this Tom Kyte's article about ROWNUM and limiting results issue.
Previous Topic: why the last value in custom forms showing with symbol
Next Topic: Error 305500 non-oracle exception with ole2 package and Outlook Application
Goto Forum:
  


Current Time: Mon Dec 05 15:00:42 CST 2016

Total time taken to generate the page: 0.09071 seconds