How will you delete all records of table other then recently added 10 records?

 Posted by Bhakti on 12/4/2009 | Category: Sql Server Interview questions | Views: 3295
Answer:

There can be many ways to get the desired results. Two of the simplest ways are described below assuming your table is having column(date1) containing value of data creation.
Way 1:
With the query below, we are deleting all the records except 10 records where id is not matching with the top most listed records based on time entry.
delete from table1

where id1 not in
(
select top 10 id1 from table1
order by date1 desc
)

Way2:
With this query we are listing records in descending way with date as key and having index more then 10. All listed records will be deleted.
delete from table1

WHERE id1 in (
SELECT id1
FROM
(
SELECT id, ROW_NUMBER() OVER(ORDER BY date1 DESC) AS rownumber
FROM table1
) AS a
WHERE rownumber > 10
)


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response