Re: TOP N Query

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