SELECTing a range from the results   

I would like to create a SELECT statement that returns a certain range of 
the results that a WHERE clause returns. For example, if the WHERE clause 
would return 75 records, I want a way to have the SELECT statement return 
the second 10 records, or the third 10 records, etc. I know how to use the 
TOP(x) clause, but that alone always returns the first x records. Is there a 
way to offset the starting point for which records to return? Thanks.
-- 
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

date: Tue, 15 Jul 2008 20:32:17 -0400   author:   Nathan Sokalski

Re: SELECTing a range from the results   

We use a technique using a CTE in combination  with the ROW_NUMBER
function to do this.  Something like this.

@PageSize and @PageIndex are passed into the procedure.

SET @PageLowerBound = @PageSize * @PageIndex + 1;
SET @PageUpperBound = @PageLowerBound + @PageSize - 1;

WITH AllRows AS (
	SELECT ROW_NUMBER() OVER (ORDER BY SortedColumn ASC)
ROW_NUMBER,
		AnotherColumn,
		ThirdColumn
	FROM YourTable)

SELECT *
FROM AllRows
WHERE ROW_NUMBER BETWEEN @PageLowerBound AND @PageUpperBound

I hope this helps.

Denny

On Tue, 15 Jul 2008 20:32:17 -0400, "Nathan Sokalski"
 wrote:

>I would like to create a SELECT statement that returns a certain range of 
>the results that a WHERE clause returns. For example, if the WHERE clause 
>would return 75 records, I want a way to have the SELECT statement return 
>the second 10 records, or the third 10 records, etc. I know how to use the 
>TOP(x) clause, but that alone always returns the first x records. Is there a 
>way to offset the starting point for which records to return? Thanks.

date: Tue, 15 Jul 2008 18:08:34 -0700   author:   Denny Cherry

Re: SELECTing a range from the results   

x-post and follow-up set to microsoft.public.sqlserver.programming

> I would like to create a SELECT statement that returns a certain range
> of the results that a WHERE clause returns. For example, if the WHERE
> clause would return 75 records, I want a way to have the SELECT
> statement return the second 10 records, or the third 10 records, etc. 

first way i can think of is simply to combine 2 top queries like

select top 10 * from (select top 65 * from table order by ordercol desc) 
foo order by ordercol asc

second is to use the row_number function to and use this in a where clause 
something like:

select * from (
select row_number() over (order by Id desc) as foo from table
) bar where foo between 10 and 20

I guess both options above will not perform too well though, but as long 
as your tables aren't huge,  that might not a big issue. For large tables, 
perhaps adding a column with the rank/row_number that you can then use in 
the where clause would be a solution?



hth
Ben
-- 
Ben Schwehn
bschwehn.de

date: Tue, 15 Jul 2008 18:10:19 -0700   author:   Ben Schwehn

Re: SELECTing a range from the results   

On Jul 16, 5:32 am, "Nathan Sokalski"  wrote:
> I would like to create a SELECT statement that returns a certain range of
> the results that a WHERE clause returns. For example, if the WHERE clause
> would return 75 records, I want a way to have the SELECT statement return
> the second 10 records, or the third 10 records, etc. I know how to use the
> TOP(x) clause, but that alone always returns the first x records. Is there a
> way to offset the starting point for which records to return? Thanks.
> --
> Nathan Sokalski
> njsokal...@hotmail.comhttp://www.nathansokalski.com/


Also search for Pagination

date: Wed, 16 Jul 2008 02:19:50 -0700 (PDT)   author:   Madhivanan