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,
`DELETE TOP 3` will not delete the first 3 rows, it will delete 3 random rows.
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
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.
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)
Thank you all it was really help full
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 ??
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
---------- 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 ??
I would recommend starting a new question rather than trying to do this within a different person's question. Please repost your problem.
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.
6 People are following this question.
