Re: Top-N query using analytic functions

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

Maybe you changed the values of deptno in scott.emp? Do you have values of 4,5 and 6 in the TOP3 column from the inner query?

Jon Received on Wed Aug 30 2006 - 08:13:08 CDT