SQL Server - how to delete last three record from the table using simple sql query

Asked By aman on 02-Nov-11 08:08 AM
hi all;

i want to delete a record from the table but i want to delte the last three record from the table.

please help me.

thanks,
Rohan Dave replied to aman on 02-Nov-11 08:14 AM
you can use below query ...

delete top 3 from yourtable order by id desc
Riley K replied to aman on 02-Nov-11 08:16 AM


Use this query

delete from orders
where orderid in (select top 3 orderid from orders order by orderid desc)

Regards
Kirtan Patel replied to aman on 02-Nov-11 08:20 AM
Try this Query
select * from emp minus select * from emp where rownum<(select count(*)-2 from emp)
Suchit shah replied to aman on 02-Nov-11 08:25 AM
u can do it like below query

delete from tablename where Fieldname in ( select Top 3 Fieldname from tablename order by fieldname)
Reena Jain replied to aman on 02-Nov-11 09:35 AM
hi,

try this and let me know

DELETE FROM Table1 WHERE ID IN (SELECT TOP 3 ID FROM Table1 order by id desc)

aneesa replied to aman on 02-Nov-11 01:12 PM
WITH OrdersRN AS
(
  SELECT *, ROW_NUMBER() OVER(ORDER BY  OrderID desc) AS RowNum
  FROM dbo.MyOrders
)
DELETE FROM OrdersRN
WHERE RowNum <= 3;