Top-N query using analytic functions

From: Martin <martin.farrow_at_e2train.com>
Date: 30 Aug 2006 02:24:29 -0700
Message-ID: <1156929869.420893.215340@m73g2000cwd.googlegroups.com>

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 Received on Wed Aug 30 2006 - 04:24:29 CDT