TOP N Query

Hi

    I have to write a query in which i get records from 11 to 20 how do i do that. I have implemented this logic in SqL server 2000. (Code below) and would like to port this code on to oracle .

   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 :))

--Harpreet

P.S. : The code from SQL Server procedure

CREATE PROCEDURE sp_getD_RBList ( @p_StartRecord int,

@p_EndRecord int,
@p_RBOnly int,
@OrderbyCriteria varchar(150)

      )

AS

DECLARE @v_SqlStmt varchar(1500)
DECLARE @v_InnerSql varchar(1500)
DECLARE @v_OuterSql varchar(1500)
DECLARE @selectCriteria varchar(500)
DECLARE @tot_recs int

 SELECT @tot_recs = Convert(int,Count (*))   FROM tbl_Institution
  WHERE DRB_Number <> '0' AND Designated = 0 AND tbl_Institution.markedforDeletion = 0

IF @tot_recs < @p_EndRecord
 SET @p_EndRecord = @tot_recs

SET @v_InnerSql = "SELECT Top " + Convert(varchar(5),@p_EndRecord) + " Institution_Name, Designated, DRB_Number, Region, Region_Name

   FROM tbl_Institution, data_Region
   WHERE tbl_Institution.Region = data_Region.Region_No AND " + @selectCriteria + " ORDER BY " + @OrderbyCriteria + " ASC "

SET @v_OuterSQL = "SELECT Top " + Convert(varchar(5),(@p_EndRecord - @p_StartRecord +1 )) + " * FROM (" + @v_InnerSQL +") InnerSql ORDER BY " + @OrderbyCriteria + " DESC"

SET @v_Sqlstmt = "SELECT * FROM (" + @v_OuterSQL + ") outersql ORDER BY " + @OrderbyCriteria + " ASC"

PRINT @v_SqlStmt
EXEC (@v_SqlStmt)

SELECT tot_recs = @tot_recs Received on Tue Dec 10 2002 - 10:01:26 CST