Home » SQL & PL/SQL » SQL & PL/SQL » how delete last 10 records from the table
how delete last 10 records from the table [message #215696] Tue, 23 January 2007 09:44 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
how delete last 10 records from the table
Re: how delete last 10 records from the table [message #215702 is a reply to message #215696] Tue, 23 January 2007 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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.
Re: how delete last 10 records from the table [message #215791 is a reply to message #215702] Tue, 23 January 2007 22:38 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi

in my table has no primary key how delete last 10 records
Re: how delete last 10 records from the table [message #215804 is a reply to message #215696] Tue, 23 January 2007 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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.
Re: how delete last 10 records from the table [message #215810 is a reply to message #215804] Wed, 24 January 2007 00:01 Go to previous messageGo to next message
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)
Re: how delete last 10 records from the table [message #215823 is a reply to message #215810] Wed, 24 January 2007 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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;
Re: how delete last 10 records from the table [message #215840 is a reply to message #215828] Wed, 24 January 2007 01:18 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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.
Re: how delete last 10 records from the table [message #215843 is a reply to message #215840] Wed, 24 January 2007 01:26 Go to previous messageGo to next message
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.
Re: how delete last 10 records from the table [message #215846 is a reply to message #215843] Wed, 24 January 2007 01:27 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
let me know any issue
thanks
srinivas pavuluri
Re: how delete last 10 records from the table [message #215852 is a reply to message #215846] Wed, 24 January 2007 01:40 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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>
Re: how delete last 10 records from the table [message #215858 is a reply to message #215852] Wed, 24 January 2007 01:57 Go to previous messageGo to next message
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
Re: how delete last 10 records from the table [message #215861 is a reply to message #215858] Wed, 24 January 2007 02:04 Go to previous messageGo to next message
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
Re: how delete last 10 records from the table [message #215892 is a reply to message #215861] Wed, 24 January 2007 03:32 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: count, characters from string
Next Topic: SQL queries . find second largest salary of emp.
Goto Forum:
  


Current Time: Wed Dec 07 20:05:44 CST 2016

Total time taken to generate the page: 0.15951 seconds