Re: Top-N query using analytic functions

Mark D Powell wrote:
> 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.
>

This is what I was expecting too, but I get the following results when I run this:

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

        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          2
        20 JONES            2975          3
        20 ADAMS            1100          4
        20 SMITH             800          5
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3

    DEPTNO ENAME             SAL       TOP3
---------- ---------- ---------- ----------
        30 WARD             1250          4
        30 MARTIN           1250          5
        30 JAMES             950          6

14 rows selected.

I haven't changed any data in that schema, and as you can see the rows with values of 4, 5 and 6 are appearing in the query.

Thanks,

Martin. Received on Thu Aug 31 2006 - 04:38:58 CDT