| delete last n rows of a table [message #77695] |
Mon, 05 November 2001 16:05  |
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  |
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 |
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 #288930 is a reply to message #288594] |
Wed, 19 December 2007 01:32 |
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 |
 |
Littlefoot
Messages: 21818
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
