db2 "with temp(row#,when_created,a,b) as (select rownumber() over() as row#
, a,b from test ) select * from temp where row# > 5"

Unfortunately the "with " construct doesn't support anything other than
SELECT so to delete using rownum

-->

create view vw1 as ( select rownumber() over() as row# , a,b from test )

delete from vw1 where row# >6
==

Ref. DB2 Cookbook for further info on ROWNUMBER and other DB2 OLAP
functions.

Praveen


-----Original Message-----
From: amit b
[mailto:db2udbdba-ezmlmshield-x39278066.[Email address protected]
Sent: 14 June 2004 15:24
To: LazyDBA Discussion
Subject: Something similar to ROWNUM in Oracle


Hi,

Can anyone tell me is there any syntax in DB2 similar to rownum in oracle.

Say in oracle i can give select * from employee where rownum > 5 or delete
from emp where rownum >6;
is there something similar to that in DB2.

Thanks,
Amit


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: db2udbdba-[Email address protected]

**********************************************************************
This email and any files transmitted with it are confidential and are
intended solely for the use of the individual or entity to whom they are
addressed. This communication represents the originator's personal views and
opinions, which do not necessarily reflect those of HPI Limited. If you are
not the original recipient or the person responsible for delivering the
email to the intended recipient, be advised that you have received this
email in error, and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited. If you received this email in
error, please immediately notify [Email address protected]

This message has been scanned by Panda Anti-Virus. Recipients are advised to
apply their own virus checks to this message on delivery.
**********************************************************************



DB2 & UDB email list listserv db2-l LazyDBA home page