Re: standard edition question...

In or last gripping episode "Terry Dykstra" <dontreply_tdykstra_at_cfol.ab.ca> wrote:
> How does rank() work? I can't find it in the documentation. If you
 give me
> an example, I can try it out on 8163 standard edition.
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
> <gdas_at_my-deja.com> wrote in message news:93nepn$mn5
 $1_at_nnrp1.deja.com...
> > I don't suppose there's any way to have enterprise edition run
> > in "standard mode?" sort of like the compatible version paramater?
> >
> > I think my answer is no, but thought I'd try anyway since it would
 save
> > me a lot of time to check on this one function.
> >
> > Thanks
> > In article <93mrnm$33h$1_at_soap.pipex.net>,
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
> > > I can't confirm that rank is unavailable. However anything which
 relies on
> > > query rewrite (which is a surprising amount) is unavailable in the
 standard
> > > edition. My guess would bbe that this might be how rank is
 implemented
> > > behind the scenes.
> > >
> > > --
> > > Niall Litchfield
> > > Oracle DBA
> > > Audit Commission UK
> > > <gdas_at_my-deja.com> wrote in message
 news:93m1uk$hrj$1_at_nnrp1.deja.com...
> > > > Is anyone running Oracle 8.1.6 standard edition?
> > > >
> > > > I found a document on oracle's web site that was explaining the
 various
> > > > differences between 8.1.6 enterprise, personal and standard
 editions...
> > > >
> > > > One thing that caught my eye was that it said that there are
 certain
> > > > sql functions like rank which are only available in Enterprise
 Edition.
> > > >
> > > > Right now we are doing development in Enterprise Edition
 because we
> > > > have a development license. Due to the fact that enterprise
 edition
> > > > costs so much more and that we won't be taking advantage of it's
 high
> > > > availability features, we will probably deploy on Standard
 Edition.
> > > > And we've been trying to make sure not to use any features
 specific
 to
> > > > Enterprise Edition.
> > > >
> > > > I'm sort of suprised that sql functions would be turned on/off
 in
 the
> > > > flavors of the database. Especially since Oracle worked with
 IBM on
> > > > the analytical functions and that they have been submitted for
> > > > inclusion as part of the SQL standard.
> > > >
> > > > Can anyone who runs standard edition on 8.1.6 or later confirm
 that
 the
> > > > rank function is disabled?
> > > >
> > > > Thanks for your time.
> > > > Gavin
> > > >
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > >
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Hopefully this will help:

SELECT s_productkey, s_amount,
  RANK() OVER (ORDER BY s_amount) AS default_rank,   RANK() OVER (ORDER BY s_amount DESC NULLS LAST) AS custom_rank FROM sales;

This statement gives:

S_PRODUCTKEY S_AMOUNT DEFAULT_RANK CUSTOM_RANK ------------ -------- ------------ -----------

SHOES                130               6              1
JACKETS               95               5              2
SWEATERS              80               4              3
SHIRTS                75               3              4
PANTS                 60               2              5
TIES                  45               1              6

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/