SQL Server - select rows m to n

[vinodar]

Hi,

I want to write a query in SQL server that returns rows from rownumber 'm' to rownumber 'n' where m and n should be changeable at runtime.

 

Pls help

 

Regards

Vinod

[mocella]

Are you trying to do some kind of paging logic with your query?

 

You could do something like this:

 

Select Top 20

From YourTable

Where SomeID >= whateverKeyYouUseToFilter

Order By yourSortCriteria

 

This way, you can just pull the first query with null or something in your Where Clause, then use the key from that last row you got this time when you exec the next query.

 

There may be another way to do this, but I can't think of it right now.

[techmanbd]

Also can do this

 

Select *

From TABLE

where rownumber between n and m

[vinodar]

Thank you mocella...it works...but there are some problems with it. In the case of a webpage listing you can't directly go to a particular page if I use this query. If you need to go to page 5, you have to take the last id of page 1 again query the database for the last id of page 2 and so on until I reach the required page.

 

rownumber keyword also doesn't work in SQL server

 

Regards

Vinod

[mocella]

If you're using Session variables, you can create an 2-D array with something like:

Page-Index, Start-Rec-ID

 

Then determine what page your user is trying to hit, look up the start record's index in the Session Array, and reload your data.

 

Something like that may do what you're looking for.