Re: TOP N Query

From: Harpreet Singh <harpreet_at_ruksun.com>
Date: Wed, 11 Dec 2002 13:05:18 +0530
Message-ID: <at6ogl$1e1$1@news.vsnl.net.in>

Hi,

   I found a solution from Oracle Work Bench (OWB). I downloaded the OWB from oracle.com, this tool is used to migrate database from other other vendors to Oracle. Then I made a dummy db in SQL Server with one table and a sp with the scenario I was facing. Using this dummy database as the source I migrated it to oracle. And OWB migrated my sp also, you wont believe the result.

SQL Server Query

---

SELECT id FROM (SELECT TOP 50 id FROM (SELECT TOP 150 id FROM emp ORDER BY id ASC) y ORDER BY id DESC) x

Oracle Query

---

SELECT * FROM (SELECT id FROM (SELECT * FROM (SELECT id FROM (SELECT * FROM (SELECT id FROM emp ORDER BY id ASC ) WHERE ROWNUM <= 150 ) "y" ORDER BY id DESC ) WHERE ROWNUM <= 50 ) "x")WHERE ROWNUM <= 15;

The above queries will give records from 51 to 150. In sql server it was 2 nested queries which grew to 5 in oracle. I am not comparing Oracle and SQL Server here, but I think the Oracle guys must have thought of such an scenario. And there must definately be a cleaner way.

regards
Harpreet
P.S. : Sorry Billy for CCing you directly.

"Science is organized knowledge. Wisdom is organized life."

"Harpreet Singh" <harpreet_at_ruksun.com> wrote in message news:at6mbb$i3$1_at_news.vsnl.net.in...
> Thank you Billy for your reply, I shall try out the method you have
> suggested.
>
> >The proper way would be to design the database and table to meet the
> > end-user requirements.. :-)
>
> I think my database design is good. And this problem can occur to
anyone.
> It's a simple one where the user would not like to see a list of all the
> employees / trainees (etc) but in a group of 10. after that the next 10
and
> so on (basically pagination). Calling all people of Oracle world to
> suggest something :)
>
> Regards
> --Harpreet
> --
> "Science is organized knowledge. Wisdom is organized life."
> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> news:at6jot$kgg$2_at_ctb-nnrp2.saix.net...
> > Harpreet Singh wrote:
> >
> > > Scenario : A web based project has few list pages (list all
> trainees,
> > > list all X ) where the trainees are listed 10 per page with next,
> > > previous, first and last page buttons. Using these buttons you can
jump
> to
> > > directly any page. This we have achieved in sql server stored
procedure
> > > using the top N clause.
> > >
> > > To get Trainees of the 4th page means from 41 to 50 trainees what
I
> do
> > > in MS Sql is get first 50 trainees (TOP 50 in asc order) and then get
10
> > > Trainees from this subset (TOP 10 in desc order) and then select these
> 10
> > > trainees. (I hope you are getting what I am trying to explain) Now my
> > > problem is how do I acheive this in Oracle :))
> >
> > The dirty way is to add a virtual sequence number to the result set.
Then
> > you set the WHERE clause to only give you rows between certain virtual
> > sequence numbers.
> >
> > Something like:
> > SELECT
> > *
> > FROM
> >
>
> > SELECT
> > rownum SEQ_NO,
> > t.*
> > FROM mytable t
> > )
> > WHERE seq_no BETWEEN 41 AND 51
> >
> > The proper way would be to design the database and table to meet the
> > end-user requirements.. :-)
> >
> > --
> > Billy
> >
> >
> >
> >
> > --
> > Billy
>
>
Received on Wed Dec 11 2002 - 01:35:18 CST