Re: Top-N query using analytic functions

jonwat wrote:
> Martin wrote:
> > I am looking into analytic functions to solve a Top-N query problem, so
> > I've been looking at some examples to understand how they work. I can't
> > seem to get the examples to behave the same way on my Oracle
> > installation, so does anyone have any idea why the following isn't
> > working?
> >
> > SELECT * FROM
> > (
> > SELECT
> > deptno,
> > ename,
> > sal,
> > ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) Top3
> > FROM emp
> > )
> > WHERE Top3 <= 3
> > /
> >
> > This example was taken from
> > http://www.akadia.com/services/ora_analytic_functions.html, and uses
> > the SCOTT schema.
> >
> > The result set should be 9 rows, but I'm getting 14 rows back
> > (basically all rows in the source table). It's as if the final WHERE
> > clause is being completely ignored.
> >
> > I'm wondering if I'm missing some patches or something? My Oracle
> > version is:
> >
> > Oracle9i Release 9.2.0.1.0 - Production
> > PL/SQL Release 9.2.0.1.0 - Production
> > CORE 9.2.0.1.0 Production
> > TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
> > NLSRTL Version 9.2.0.1.0 - Production
> >
> > Martin

>

Using the traditional EMP table on version 9.2.0.6 running on AIX 5.2 I got:

UT1 > @t8

    DEPTNO ENAME SAL TOP3 ---------- ---------- ---------- ----------

        10 KING             5000          1
        10 CLARK            2450          2
        10                  1400          3
        20 SCOTT            3000          1
        20 FORD             3000          2
        20 JONES            2975          3
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3

9 rows selected.

Barring where I might have changed a data value the output looks correct for what was asked.

HTH -- Mark D Powell -- Received on Wed Aug 30 2006 - 09:19:15 CDT