Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!nntp2.aus1.giganews.com!nntp.giganews.com!wn12feed!worldnet.att.net!204.71.34.3!newsfeed.cwix.com!news.vsnl.net.in!not-for-mail From: "Harpreet Singh" Newsgroups: comp.databases.oracle.misc Subject: Re: TOP N Query Date: Thu, 12 Dec 2002 11:33:23 +0530 Organization: VSNL Lines: 36 Message-ID: References: NNTP-Posting-Host: mail.ruksun.com X-Trace: news.vsnl.net.in 1039762633 259 202.54.40.130 (13 Dec 2002 06:57:13 GMT) X-Complaints-To: postmaster@news.vsnl.net.in NNTP-Posting-Date: Fri, 13 Dec 2002 06:57:13 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4920.2300 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300 Xref: newsfeed1.easynews.com comp.databases.oracle.misc:90518 X-Received-Date: Fri, 13 Dec 2002 00:29:35 MST (news.easynews.com) 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" wrote in message news: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