Re: TOP N Query

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 11 Dec 2002 15:07:06 +0200
Message-ID: <at7ddn$2kd$1@ctb-nnrp2.saix.net>

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

Received on Wed Dec 11 2002 - 07:07:06 CST