Can you use Rank to get rows 51 to 60 of a query?

I have not installed SQL Server 2005 yet (hopefully this weekend), so I have not been able to experiment yet.

One of the things I had hoped would be added to T-SQL is a way to get a range of rows out the middle of a query to populate a table, e.g. when you are showing the user page 6 of many. I had been hoping for a variation of SELECT TOP something like:

SELECT ROWS 51 TO 60 *
FROM MyTable
WHERE State = 'CA'
ORDER BY CustomerNumber

I don't see anything like that. Is there a way to do that I have not seen

Can I use RANK in the where clause to do this

My backup plan would be to do a subquery with rank in it, and put the where in the the outer query, but it seems like the same logic that returns top 10 could just as easily return a specific 10 out of the middle.

Thanks.

Answer this question

Can you use Rank to get rows 51 to 60 of a query?

w4t4fAk
Thanks!

It was very useful!!
sy_mbm
There is no syntax to do paging directly. RANK is not meant to paging rows. You can use ROW_NUMBER instead like:

with t
as
(
select *, row_number() over(order by CustomerNumber) as seq
from MyTable
where State = 'CA'
)
select * from t
where seq between 51 and 60;

Performance of this query depends on your indexes/schema. Alternatively, you can select the matching rows into a temporary table with identity column and then filter on the identity value. (this is how you would do it in SQL Server 2000 also)

lasu

I think ROW_NUMBER() is more effective here. See Itzik's article:
http://www.windowsitpro.com/Article/ArticleID/43922/43922.html



> One of the things I had hoped would be added to T-SQL is a way to get a
> range of rows out the middle of a query to populate a table, e.g. when
> you are showing the user page 6 of many. I had been hoping for a
> variation of SELECT TOP something like:
>
> SELECT ROWS 51 TO 60 *
> FROM MyTable
> WHERE State = 'CA'
> ORDER BY CustomerNumber
>
> I don't see anything like that. Is there a way to do that I have not
> seen 
>
> Can I use RANK in the where clause to do this

Matthew Owens

> Performance of this query depends on your indexes/schema. Alternatively,
> you can select the matching rows into a temporary table with identity
> column and then filter on the identity value. (this is how you would do
> it in SQL Server 2000 also)

After sending my post and reading yours, I realized I should have also 
stated that there are several ways to skin this cat, and varying 
index/schema designs may yield different "optimal" solutions. While the 
article still has not been updated to add the methods available in SQL 
Server 2005, even on that platform it may be worth giving some of these 
other methods a try: http://www.aspfaq.com/2120

Can you use Rank to get rows 51 to 60 of a query?

Answer this question