Delete the rows based on their rownumbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete the rows based on their rownumbers

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-04 : 20:49:38

Hello Experts,
If some one please point me how to delete the rows based on the row numbers.
Currently I am using two work tables and then inserting the rownumber values and then deleting.
Takes some extra work.
I used the rownumber function to delete.But it gives the Error.
DELETE WORKTABLE WORKTABLE
where ROW_NUMBER()OVER(ORDER BY RAND()) in (1,2,3)
Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

I would really appreciate the Help!

Thanks!
Raju

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-04 : 20:58:46

DELETE WORKTABLE
from WORKTABLE w
join (select pk, rownum = ROW_NUMBER()OVER(ORDER BY RAND()) from WORKTABLE) a
on a.pk = w.pk
where a.rownum in (1,2,3)

Of course this will always delete the same rows - rand() doesn't do anything.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-08-04 : 21:48:07

It did the Trick.
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources