Re: Selects only the first 200 records aftering ordering from a table.

Hi Collin,

this solution comes from jurij.modic_at_mf.sigov.mail.si (Jurij Modic):

SELECT a.deptno, a.sal sumsal, ROWNUM ranking FROM

    (SELECT deptno, SUM(sal) sal FROM emp GROUP BY deptno) a,      dual
  WHERE -1*a.sal = DECODE(dual.dummy(+),'X',0,0)   AND ROWNUM <= 3
/

You are right that ROWNUM is applied before any *explicit* sorting is done (like ORDER BY or GROUP BY), but if Oracle has to perform any *implicit* sorting, then ROWNUM is applied on the sorted resultset! I made use of this fact in my sollution, posted a few days ago (see also below).

All the above comes from Juri.

Colin Woods schrieb:
>
> Folks,
>
> Can anyone help me ?
>
> I have a large table, where I only want to select up to a maximum of 500
> records.
>
> I can't use ROWNUM because I want to perform an ORDER BY on the table
> before taking out the first 500 records.
>
> The statement I has was
>
> SELECT A, B, C
> FROM table
> WHERE Condition
> AND rownum < 500
> ORDER BY C, B
>
> What this select does is select 500 records, and then orders them.
>
> What I want is to order all records first, and then return the first
> 500(after ordering).
>
> Anyoue any ideas ?
>
> P.S. I don't want to use PL/SQL and a CURSOR select if possible !.
>
> THANKS
>
> Colin Woods.

--

Regards

Matthias Gresz :-) Received on Thu Apr 16 1998 - 09:39:03 CDT