Prev: on allowing triggers to fire others
Next: build forecast
From: Nathan Sokalski on 15 Jul 2008 20:32
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(a)hotmail.com
http://www.nathansokalski.com/


From: Denny Cherry on 15 Jul 2008 21:08
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"
<njsokalski(a)hotmail.com> 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.
From: Ben Schwehn on 15 Jul 2008 21:10
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
From: Madhivanan on 16 Jul 2008 05:19
On Jul 16, 5:32 am, "Nathan Sokalski" <njsokal...(a)hotmail.com> 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...(a)hotmail.comhttp://www.nathansokalski.com/


Also search for Pagination
 | 
Pages: 1
Prev: on allowing triggers to fire others
Next: build forecast