CODEWITHSUNDEEP
sqldb2udb
user3396478
user3396478

Reputation: 232

Keep First N number of rows and delete the rest

trying to write a sql which will keep first N number of rows of a table and delete the rest. I'm have comeup with this sql but the its saying I can't use the count here. Please help me to re write the sql. 

DELETE 
  FROM ZZ_TEST_FINTABLE
 WHERE PROCESS_INSTANCE = ( 
 SELECT MIN(B.PROCESS_INSTANCE) 
  FROM ZZ_TEST_FINTABLE B) 
   AND COUNT(PROCESS_INTANCE) > 9

Upvotes: 2

Views: 1659

Answers (3)

crig
crig

Reputation: 879

You'll have to modify this for your table, but should work:

DELETE FROM myschema.mytable WHERE pkey NOT IN (SELECT pkey FROM myschema.mytable ORDER BY pkey FETCH FIRST 10 ROWS ONLY)

Upvotes: 0

Orhun D.
Orhun D.

Reputation: 637

Maybe this works for you (with Oracle DB)

DELETE FROM
    ZZ_TEST_FINTABLE 
WHERE
    PROCESS_INSTANCE NOT IN 
    (
        SELECT PROCESS_INSTANCE 
        FROM ZZ_TEST_FINTABLE 
        WHERE ROWNUM < 9
    );

Upvotes: 0

Karim Daraf
Karim Daraf

Reputation: 226

You should use HAVING instead of AND.

   DELETE 
   FROM ZZ_TEST_FINTABLE
   WHERE PROCESS_INSTANCE = ( 
                            SELECT MIN(B.PROCESS_INSTANCE) 
                             FROM ZZ_TEST_FINTABLE B
                            ) 
   HAVING COUNT(PROCESS_INTANCE) > 9

or this

    DELETE 
    FROM ZZ_TEST_FINTABLE A
    INNER JOIN ZZ_TEST_FINTABLE B ON A.PROCESS_INSTANCE= MIN(B.PROCESS_INSTANCE)
    HAVING COUNT(PROCESS_INTANCE) > 9

Upvotes: 0

Related Questions