none
Select rows apart from the top 1000 rows

    Question

  • I have a situation where i have to select and delete all the rows from a table apart from the top 1000 rows. How can I use a delete query for this? Please help.

    mayooran99

    Friday, January 02, 2015 3:19 PM

Answers

All replies

  • How do you identify the top 1000?  Depending on how you order the set will determine the 1000 rows you keep.

    You could use a ranking function over the data to give you a unique identifier for each row, then delete from the table where the row's not in the ranked set.

    Anyway, please post DDL and sample data for more help


    Thanks, Andrew
    My blog...

    Friday, January 02, 2015 3:34 PM
    Answerer
  • @Andrew Below shown is how I choose the top 1000,

    select top(1000) *
    from ats
    order by atsID

    How would I apply your concept to this? Please give an example


    mayooran99

    Friday, January 02, 2015 3:39 PM
  • something like this

    To select all the rows except first 1000 rows

    select * from dbo.tablename
    except
    select top 100 * from dbo.tablename
    

    I agree with Andrew, how do you identify the first 1000 rows. Do you have unique field to identify top records?

    --Prashanth

    Friday, January 02, 2015 3:49 PM
    Answerer
  • DELETE FROM ats
    WHERE atsid NOT IN (
    SELECT top 1000 atsid
    FROM ATS b ORDER BY ATSID ASC)

    Thanks, Andrew
    My blog...

    Friday, January 02, 2015 3:51 PM
    Answerer
  • @Andrew Below shown is how I choose the top 1000,

    select top(1000) *
    from ats
    order by atsID

    How would I apply your concept to this? Please give an example


    mayooran99

    If this is how you select top 1000 then you can use any of the below for deleting the other records

    1.

    DELETE t
    FROM  (select row_number() over (order by atsID) AS Seq 
    from ats
    )t
    WHERE Seq > 1000

    2.

    DELETE t
    FROM ats t
    where EXISTS
    (
    select 1
    from ats
    where atsID > t.atsID
    having count(*) > 1000
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 02, 2015 4:48 PM
    Moderator
  • If you have a big table please use this query:

    CREATE TABLE T ( Id INT PRIMARY KEY) ;
    go
    INSERT T
    VALUES  ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) 
    -------------
    
    SELECT TOP 2 *
    INTO #temp
    FROM T
    ORDER BY Id
    
    --fast delete
    TRUNCATE TABLE dbo.T
    
    INSERT dbo.T
    SELECT *
    FROM #temp ;
    
    SELECT *
    FROM T
    

    If your table is not too big and your SQL Server version is 2012 or above, you can use this query which has a good execution plan. 

    DECLARE @qty INT = 2	--1000
    DECLARE @T TABLE ( Id INT PRIMARY KEY) ;
    
    INSERT @T
    VALUES  ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) 
    
    
    
    ;WITH cte AS 
    (
    SELECT Id
    FROM @T
    ORDER BY Id
    OFFSET @qty ROWS 
    )
    DELETE cte
    
    
    SELECT *
    FROM @T



    T-SQL Articles
    T-SQL e-book by TechNet Wiki Community
    T-SQL blog

    Friday, January 02, 2015 10:43 PM
    Moderator
  • Hello ,

    Try the following code :

    Delete/Truncate from tbltest where not in
    ( 
       Select top 1000 * from tbltest
    )


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, January 03, 2015 8:01 PM