Re: TOP N Query

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