Need to delete first n rows

Shefu · #1 (**permalink**) 11-18-05, 04:23

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

gardenman · #2 (**permalink**) 11-18-05, 05:32

do like this:

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

juliane26 · #3 (**permalink**) 11-18-05, 10:18

But Shefu, what is the purpose of that ??

Peter.Vanroose · #4 (**permalink**) 11-18-05, 17:35

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 )

Internet Services

Web Development

Digital Marketing

Consumer Tech