Thiru,

This works under DB2 for Linux/Windows/Unix V7.x or later. DB2 for
z/OS(OS/390) at present doesn't support these OLAP functions.
However you could write equivalent joins to generate row numbers though it
is complex.

Ref. DB2 Cookbook for an example.

Praven

-----Original Message-----
From: Thirumaran [mailto:[Email address protected]
Sent: 14 June 2004 16:17
To: PRAVEEN.[Email address protected]
Subject: Re: Something similar to ROWNUM in Oracle


**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text


hi praveen

what version of db2 this supports under what operating system

thanks & regards
thiru
----- Original Message -----
From: "Praveen Chengalathu "
<db2udbdba-ezmlmshield-x79024061.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, June 14, 2004 8:36 PM
Subject: RE: Something similar to ROWNUM in Oracle


>
> 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.
> **********************************************************************
>
>
>
>
> ---------------------------------------------------------------------
> 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