OraFAQ Forum: SQL & PL/SQL » how delete last 10 records from the table

Home » SQL & PL/SQL » SQL & PL/SQL » how delete last 10 records from the table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how delete last 10 records from the table [message #215696]

Tue, 23 January 2007 09:44

pavuluri
Messages: 247
Registered: January 2007

Senior Member

how delete last 10 records from the table

Report message to a moderator

Re: how delete last 10 records from the table [message #215702 is a reply to message #215696]

Tue, 23 January 2007 10:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>last 10 records from the table
In any RDBMS no such concept of "last 10 records" exists!
Records are returned to client in an indeterminate order.

Report message to a moderator

Re: how delete last 10 records from the table [message #215791 is a reply to message #215702]

Tue, 23 January 2007 22:38

pavuluri
Messages: 247
Registered: January 2007

Senior Member

hi

in my table has no primary key how delete last 10 records

Report message to a moderator

Re: how delete last 10 records from the table [message #215804 is a reply to message #215696]

Tue, 23 January 2007 23:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>in my table has no primary key how delete last 10 records
How do YOU identify the "last 10 records"?
Then do so & DELETE them.

Report message to a moderator

Re: how delete last 10 records from the table [message #215810 is a reply to message #215804]

Wed, 24 January 2007 00:01

pavuluri
Messages: 247
Registered: January 2007

Senior Member

yes i got it.
its works

u can try this

delete from employee where rowid in
(select r from (select rowid r from employee order by rownum desc) where rownum<4)

Report message to a moderator

Re: how delete last 10 records from the table [message #215823 is a reply to message #215810]

Wed, 24 January 2007 00:45

Frank
Messages: 7901
Registered: March 2000

Senior Member

... which deletes three RANDOM rows.
You can just as well remove the 'order by rownum'. That doesn't do anything.
Your query is exactly the same as

delete employee where rownum < 4;

Search the board for top-n. This question has been answered a zillion times.

[Updated on: Wed, 24 January 2007 00:46]

Report message to a moderator

Re: how delete last 10 records from the table [message #215828 is a reply to message #215823]

Wed, 24 January 2007 00:52

pavuluri
Messages: 247
Registered: January 2007

Senior Member

delete from employee where rowid in
(select r from (select rowid r from employee order by rownum desc) where rownum<4)

its working fine see once agian;

Report message to a moderator

Re: how delete last 10 records from the table [message #215840 is a reply to message #215828]

Wed, 24 January 2007 01:18

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Not that it isn't working, but you'd get the same result using Frank's statement. Plus, on large tables, your query is about to last longer than Frank's.

Report message to a moderator

Re: how delete last 10 records from the table [message #215843 is a reply to message #215840]

Wed, 24 January 2007 01:26

pavuluri
Messages: 247
Registered: January 2007

Senior Member

see the out put

SQL> select * from employee;

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto
Programmer

02 Alison Mathews 21-MAR-76 21-FEB-86 2334.78 Vancouver
Tester

03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver
Tester

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04 Celia Rice 24-OCT-82 21-APR-99 2334.78 Vancouver
Manager

05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver
Tester

06 Linda Green 30-JUL-87 04-JAN-96 2334.78 New York
Tester

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York
Manager

08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver
Tester

08 James Cat 17-SEP-96 15-APR-02 1234.56 Vancouver
Tester

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
08 James Cat 17-SEP-96 15-APR-02 1234.56 Vancouver
Tester

10 rows selected.

SQL> delete from employee where rowid in
2 (select r from (select rowid r from employee order by rownum desc) where rownum<4);

3 rows deleted.

SQL> select * from employee
2 ;

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto
Programmer

02 Alison Mathews 21-MAR-76 21-FEB-86 2334.78 Vancouver
Tester

03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver
Tester

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04 Celia Rice 24-OCT-82 21-APR-99 2334.78 Vancouver
Manager

05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver
Tester

06 Linda Green 30-JUL-87 04-JAN-96 2334.78 New York
Tester

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York
Manager

7 rows selected.

Report message to a moderator

Re: how delete last 10 records from the table [message #215846 is a reply to message #215843]

Wed, 24 January 2007 01:27

pavuluri
Messages: 247
Registered: January 2007

Senior Member

let me know any issue
thanks
srinivas pavuluri

Report message to a moderator

Re: how delete last 10 records from the table [message #215852 is a reply to message #215846]

Wed, 24 January 2007 01:40

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Different execution plans: first, Frank's DELETE:

SQL> delete emp where rownum < 4;

3 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3588877096

--------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT    |      |     3 |     3   (0)| 00:00:01 |
|   1 |  DELETE             | EMP  |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<4)

And now, your code:

SQL> delete emp
  2  where rowid in (select r from (select rowid r from emp
  3                                 order by rownum desc)
  4                  where rownum < 4);

3 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1243628275

-----------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |          |     1 |    24 |     6  (34)| 00:00:01 |

|   1 |  DELETE                      | EMP      |       |       |            |          |

|   2 |   NESTED LOOPS               |          |     1 |    24 |     6  (34)| 00:00:01 |

|   3 |    VIEW                      | VW_NSO_1 |     3 |    36 |     4  (25)| 00:00:01 |

|   4 |     SORT UNIQUE              |          |     1 |    36 |            |          |

|*  5 |      COUNT STOPKEY           |          |       |       |            |          |

|   6 |       VIEW                   |          |    14 |   168 |     4  (25)| 00:00:01 |

|*  7 |        SORT ORDER BY STOPKEY |          |    14 |   168 |     4  (25)| 00:00:01 |

|   8 |         COUNT                |          |       |       |            |          |

|   9 |          TABLE ACCESS FULL   | EMP      |    14 |   168 |     3   (0)| 00:00:01 |

|  10 |    TABLE ACCESS BY USER ROWID| EMP      |     1 |    12 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(ROWNUM<4)
   7 - filter(ROWNUM<4)


SQL>

Report message to a moderator

Re: how delete last 10 records from the table [message #215858 is a reply to message #215852]

Wed, 24 January 2007 01:57

pavuluri
Messages: 247
Registered: January 2007

Senior Member

delete * from emp rownum<4

its delete only 1st rows.

but my reqirement is

we want delete last 3 rows.

thats whay useing those query.

otherwise u can go for tiger.

thanks
srinivas

Report message to a moderator

Re: how delete last 10 records from the table [message #215861 is a reply to message #215858]

Wed, 24 January 2007 02:04

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
@littefoot
@frank
misunderstood your question.

yes your query is correct if LAST ROWS delete.
or
Frank query is correct if FAST ROWS delete

regards
Taj

Report message to a moderator

Re: how delete last 10 records from the table [message #215892 is a reply to message #215861]

Wed, 24 January 2007 03:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

@pavuluri: I'm with anacedent. You have to specify an ORDER BY. And ORDER BY ROWNUM does NOT make sense. ROWNUM is a pseudo column that gets assigned after a record is fetched. If you fetch without an ORDER BY, the ROWNUM assignment is unpredictable. By consequence, the ORDER BY ROWNUM is unpredictable too.

link
link
link
link

MHE

[Updated on: Wed, 24 January 2007 03:35]

Report message to a moderator

Re: how delete last 10 records from the table [message #215897 is a reply to message #215861]

Wed, 24 January 2007 04:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

user52 wrote on Wed, 24 January 2007 09:04

Hi,
@littefoot
@frank
misunderstood your question.

yes your query is correct if LAST ROWS delete.
or
Frank query is correct if FAST ROWS delete

regards
Taj

No Taj, we did NOT misunderstand his question. There is NO such thing as LAST ROWS if you order by rownum. Like Maarten says, you need an actual column to order by, not a pseudo column.
Both the OPs query and mine delete RANDOM rows.

Report message to a moderator

Previous Topic:	count, characters from string
Next Topic:	SQL queries . find second largest salary of emp.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 07 12:36:57 CDT 2025