Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: TOP N Query
http://asktom.oracle.com/pls/ask/f?p=4950:8:1716280
Found a very good solution.
--Harpreet
-- "Science is organized knowledge. Wisdom is organized life." "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:at7ddn$2kd$1_at_ctb-nnrp2.saix.net...Received on Thu Dec 12 2002 - 00:03:23 CST
> Harpreet Singh wrote:
>
>
> > I tried the following query
> >> SELECT
> >> id
> >> FROM
> >> ( SELECT rownum SEQ, id FROM emp ORDER BY surname)
> >> WHERE seq BETWEEN 4 AND 11
> > And it worked, it gave the rows correctly from 4 to 11 even after the
> > order by clause. I think it must be first sorting the records in the db
> > temp space (somewhere) and then fetching it. And while fetching it
> > assigning row nums.
>
> Ah.. so it does. Hmm.. I was pretty certain that it did not work. Think I
> got confused with order by vs. top n queries with group by clauses.
>
> Anyway, it does make things simpler seeing that I'm wrong.
>
> The only problem with this approach is that you effectively perform a full
> table scan each time the query is run (even though only a subset of the
> result set is returned). Not a nice thing when dealing with a large table.
>
> --
> Billy