SELECTing a range from the results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nathan Sokalski - 16 Jul 2008 01:32 GMT

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.

Signature

Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

Reply to this Message

Denny Cherry - 16 Jul 2008 02:08 GMT

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

>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.

Reply to this Message

Ben Schwehn - 16 Jul 2008 02:10 GMT

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

Signature

Ben Schwehn
bschwehn.de

Reply to this Message

Madhivanan - 16 Jul 2008 10:19 GMT

> 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
[quoted text clipped - 5 lines]
> Nathan Sokalski
> njsokal...@hotmail.comhttp://www.nathansokalski.com/

Also search for Pagination

Reply to this Message