"Ken" <eubangus@gmail.com> wrote in message
news:1129787161.514990.150290@g44g2000cwa.googlegroups.com...
>I know this is a well-trodden road, but I've come up with a SQL paging
> solution that I can't seem to match up with the many approaches I've
> seen online. Feedback on "my" approach would be appreciated.
>
> The following statement to get the TOP x rows, and we add a criteria
> which will exclude the rows we've already retrieved. The goal is to
> get the first x rows starting at row r. My current query is:
>
> SELECT TOP x *
> FROM Table
> WHERE IDColumn NOT IN ( SELECT TOP r IDColumn
> FROM Table
> ORDER BY SortColumn)
> ORDER BY SortColumn;
>

"Ken" <eubangus@gmail.com> wrote in message
news:1129787161.514990.150290@g44g2000cwa.googlegroups.com...
>I know this is a well-trodden road, but I've come up with a SQL paging
> solution that I can't seem to match up with the many approaches I've
> seen online. Feedback on "my" approach would be appreciated.
>
> The following statement to get the TOP x rows, and we add a criteria
> which will exclude the rows we've already retrieved. The goal is to
> get the first x rows starting at row r. My current query is:
>
> SELECT TOP x *
> FROM Table
> WHERE IDColumn NOT IN ( SELECT TOP r IDColumn
> FROM Table
> ORDER BY SortColumn)
> ORDER BY SortColumn;
>

"David Portas" wrote:

> An example of this method is given in:
> http://www.aspfaq.com/show.asp?id=2120
> (the section headed "Dynamic SQL").
>
> Another potential catch is that TOP (without the TIES option) isn't
> deterministic unless you specify a unique key in the ORDER BY list.
> That means you could miss or duplicate rows between pages (although the
> same potential problem is common to other paging methods). Make sure
> you specify a unique sort order.
>
> --
> David Portas
> SQL Server MVP
> --
>