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
 >> Stay informed about: SELECTing a range from the results 

On Jul 16, 5:32 am, "Nathan Sokalski" <njsokal... DeleteThis @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... DeleteThis @hotmail.comhttp://www.nathansokalski.com/


Also search for Pagination
 >> Stay informed about: SELECTing a range from the results