Retrieve row number X to row Y

[Nicolasb]

Hi!

My query: SELECT * FROM table
Return 100 rows

I want to retrieve records from row number 20 to
row number 30...

How can I do this?
A select within a select?
With variables?

Thank you very much,
Nicolas

 

[MichaelRed]

The 'usual' approach would be to determine the criteria which would return a SET of results which would meet you needs. In some extreme situations, you may want to limit the number of records to the "Top" N. This would provide the limit of eleven records you mention (20 to 30), provided the 20th record were the first record.

IF we [red]ASSSUME[/red] that the records are sequentially numbered, a query with the criteria "[RecNo] >= 20", which was also ORDERED by RecNo, with the parameter TOP = 11 would return the results desceibed.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Nicolasb]

Hi Michael,

In my case, I cannot use the TOP clause because my ORDER
BY is not by uniqueID. Instead, I have written a store
procedure. I'll post it here, maybe it will be usefull,
someday, for another user.

Thanx for you're answer,
Nicolas

- - - - - - - - - -

/*
Receive the number of the page were the user is.
This number is sent via HTTP GET or POST.
This store procedure is calle within a .asp page.
*/
CREATE Procedure sp_RechercheMembre
(
@intPage integer
)
As


DECLARE @intLoopCounter int
DECLARE @rowToFetch int
DECLARE @startingRow int
DECLARE @intAbsolute int


/*
@rowToFetch : YOU CAN CHANGE THIS VARIABLE
it is the number of row that this procedure
is going to return.
*/
SET @intLoopCounter = 0
SET @rowToFetch = 5
SET @startingRow = (@intPage - 1) * @rowToFetch


/*
Initial request.
We're going to retrieve row X to row Y
of this initial request
*/
DECLARE recherche_cursor SCROLL CURSOR FOR
SELECT NoMembre, Nom, Prenom FROM Internaute
ORDER BY Nom

OPEN recherche_cursor

SET @intAbsolute = @startingRow
WHILE @intLoopCounter < @rowToFetch
BEGIN
SET @intLoopCounter = @intLoopCounter + 1
SET @intAbsolute = @intAbsolute + 1
FETCH ABSOLUTE @intAbsolute
FROM recherche_cursor
END

Close recherche_cursor
Deallocate recherche_cursor

Return 0