Keep First N number of rows and delete the rest

Question

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

Which database are you using? Please tag your question appropriately. — Gordon Linoff, Sep 16 '14 at 13:02
You can try using LIMIT keyword like given below:DELETE FROM ZZ_TEST_FINTABLE LIMIT 10, 100 — Hansraj, Sep 16 '14 at 13:04

score 0 · Answer 1 · answered Sep 16 '14 at 13:02

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

This does not look like correct syntax. Did you test this to work? — mustaccio, Sep 16 '14 at 13:27

Orhun D. · Accepted Answer · 2014-09-16T13:23:11.810

0

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
    );

edited Sep 16 '14 at 13:23

answered Sep 16 '14 at 13:17

Orhun D.

589
7
14

score 0 · Answer 3 · answered Sep 16 '14 at 22:12

0

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)

answered Sep 16 '14 at 22:12

crig

859
6
19

Keep First N number of rows and delete the rest

3 Answers3