question

Sammy avatar image
Sammy asked

Deleting last n rows from a table

Hi I am new to Sql , Heard this forum is really help full for learning. can any one tell me how to solve it,
sql-server-2008-r2
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

`DELETE TOP 3` will not delete the first 3 rows, it will delete 3 random rows.
2 Likes 2 ·
i have created a table name orders . i am trying to delete last 3 rows form this table, i have used TOP for deleting first 3 rows , delete top(3) from orders i want to delete last 3 rows orderid customerid employeeid 1 10248 VINET 5 2 10249 TOMSP 6 3 10250 HANAR 4 4 10251 VICTE 3 5 10252 SUPRD 4 6 10253 HANAR 3 7 10254 CHOPS 5 8 10255 RICSU 9 9 10256 WELLI 3 10 10257 HILAA 4
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered
One of the key ways to benefit from this, or any other, forum is to ask a well defined question that is something that you are having a problem with. Which part of deleting rows is posing you a problem? What code have to tried so far? How do you define the 'last n rows'? Can you please give us more to go on so that we can help explain the way you will be able to proceed and so that you understand what you are doing.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
If you are determining the "first" and "last" rows by the orderid and orderid is unique, then you could use first 3: delete from orders where orderid in (select top 3 orderid from orders order by orderid asc) last 3: delete from orders where orderid in (select top 3 orderid from orders order by orderid desc)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sammy avatar image
Sammy answered
Thank you all it was really help full
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mayuri avatar image
Mayuri answered
what if the rows are not arranged according to the orderid and then we just want to delete last 3 rows ?? How this can be done ??
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How do you define which the bottom three rows are? Figure that one out and then use @Kev's suggestion. Alternatively, you could use a CTE, something like: With MyCTE AS (SELECT Field1, Field2, ..., ROW_NUMBER() OVER (ORDER BY whatever) As RowNum FROM MyTable ) DELETE FROM MyCTE WHERE RowNum <= 3 For more on ROW_NUMBER, see http://technet.microsoft.com/en-us/library/ms186734.aspx. For more on the WITH statement and CTEs, see http://technet.microsoft.com/en-us/library/ms175972.aspx
0 Likes 0 ·
Mayuri avatar image
Mayuri answered
---------- Look at the following tables: "Employees_Norway": E_ID E_Name 01 Hansen, Ola 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari "Employees_USA": E_ID E_Name 01 Turner, Sally 02 Kent, Clark 03 Svendson, Stephen 04 Scott, Stephen By mistake I did this :- "Employees_Norway": E_ID E_Name 01 Hansen, Ola 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari 01 Turner, Sally 02 Kent, Clark 03 Svendson, Stephen 04 Scott, Stephen Now i want to delete these last 4 rows from this table and create a new table (Employees_USA) and transfer these four rows into this table ........... How this can be done ??
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would recommend starting a new question rather than trying to do this within a different person's question. Please repost your problem.
0 Likes 0 ·
amitesh avatar image
amitesh answered
If the rows are not arranged according to the orderid and we dont know how many recoreds are there then we just want to delete last 3 rows ?? How this can be done.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Given that this is an old thread, I'd recommend starting a new question instead of trying to revive this particular one. Please post a new question. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.