"select rows @start through @end" how to do it?

Thought I'd post the whole problem I'm trying to solve -- seems like it's
common enough that there must be a "normal" way to do this.
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
So I thought I'd do it with a UDF, but I thought I'd see if there was a good
one already written for this.
Thanks!Jesse wrote:
> Thought I'd post the whole problem I'm trying to solve -- seems like
> it's common enough that there must be a "normal" way to do this.
> I want to say "select the top 2000 rows, starting at row 15000, from
> myTable order by foo". I know I could use SELECT TOP 17000... to
> limit the number of rows returned, but I'm still pulling a ton more
> data than I need.
> So I thought I'd do it with a UDF, but I thought I'd see if there was
> a good one already written for this.
> Thanks!
Select Top 2000
From dbo.MyTable
Where MyCol >= 15000
Order By Foo
Unless you have some column in the table that is numbered, there is no such
thing as "row 15000". Rows are not positional. In SQL 2005, you can assign
row numbers to the columns in the query using hte ROW_NUMBER() function.
David Gugick
Quest Software|||Thanks, David --
You're right, if the query isn't sorted. But if you specify an ORDER BY
clause, then there is a definite Nth row. (If the data changes, the Nth row
may change as well, but for my purposes, that's OK.)
In your example, if you can stop at the 2,000th row returned from an ordered
query (SELECT TOP 2000), it follows that you could skip over the first N of
them.
Jesse

> Select Top 2000
> From dbo.MyTable
> Where MyCol >= 15000
> Order By Foo
> Unless you have some column in the table that is numbered, there is no
> such thing as "row 15000". Rows are not positional. In SQL 2005, you can
> assign row numbers to the columns in the query using hte ROW_NUMBER()
> function.
>
> --
> David Gugick
> Quest Software
>