Re: fist three higher values

From: Bricklen Anderson <bricklen13_at_hotmail.com>
Date: 31 Jan 2002 09:56:55 -0800
Message-ID: <b416ca2d.0201310956.40092875_at_posting.google.com>

rownum is going to throw you off in certain situations. Try using a SQL analytic function instead (it you're using 8.1.6+), like so:

select *
from (select deptno, ename, sal, dense_rank()

      over (partition by deptno
            order by sal desc)
      dr from emp)

where dr <= 3
order by deptno, sal desc
/

(taken directly from Tom Kyte's book, Expert one-on-one Oracle, p.566. also check his site for numerous examples of analytic functions, and top N queries, http://asktom.oracle.com )

I've run into the same question that you have and these functions are great -- huge performance differences.

cheers

Brick

"David A. Levy" <david.a.levy_at_rogers.com> wrote in message news:<_1468.8516$liz.5891_at_news2.bloor.is>...
> Here is one example - using the EMP table in the SCOTT schema
>
> SQL> describe scott.emp
> Name Null? Type
> ----------------------------------------- -------- ------------------------
> ----
> EMPNO NOT NULL NUMBER(4)
> ENAME VARCHAR2(10)
> JOB VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL NUMBER(7,2)
> COMM NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> SQL> select sal from scott.emp
> 2 where rownum < 4
> 3 order by sal desc;
>
> SAL
> ----------
> 1600
> 1250
> 800
>
> SQL>
Received on Thu Jan 31 2002 - 18:56:55 CET