Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Need to delete first n rows

    Need to delete first n rows at a time <B>FROM A SET OF ROWS </B> that are returned, I am not able to use ROW_NUMBER() OVER() in a delete statement..

    Eg.

    delete from TableName where col1 > 10 and col2 <20

    I wanna delete the first row from the result set returned by this delete statement.

    I tried with,

    delete from TableName where col1 > 10 and col2 <20 and ROW_NUMBER() OVER() < 2

    but it fails..



    thanks
    Shefu

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    do like this:

    delete from (select * from crm.person fetch first 1000 rows only)

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    But Shefu, what is the purpose of that ??
    Juliane

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Shefu
    Need to delete first n rows
    If you mean the alphabetically (or numerically) first n rows when the table is sorted on column col1, do the following:
    Code:
    DELETE FROM TableName
    WHERE  col1 < ( SELECT a.col1
                    FROM TableName AS a INNER JOIN TableName AS b ON a.col1 > b.col1
                    GROUP BY a.col1 HAVING count(*) = n )
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •