Common Paging Practice

Is there a method used to limit the returned number of records from a
query, useful for paging, that does not involve the process of creating
a temp table with an identity field and limiting results by that.See if this helps:
How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
AMB
"jw56578@.gmail.com" wrote:

> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||Depending on what application you are using to display the resultset
(ASP.NET, MS Access, Crystal, etc), you will have different options for
paging at the client side. For a pure SQL solution, let's assume you have a
table (mytable) that contains a record for each zip code for each state, and
you want to page 10 zip codes at a time for the state of Arizona.
Page zip codes 31 - 40:
select bottom 10 zip_code from (select top 40 zip_code from mytable where
state_code='AZ' order by zip_code) as x
Page zip codes 41 - 50:
select bottom 10 zip_code from (select top 50 zip_code from mytable where
state_code='AZ' order by zip_code) as x
<jw56578@.gmail.com> wrote in message
news:1124732360.078016.240550@.f14g2000cwb.googlegroups.com...
> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||The subquery solution is fine for 50 rows, but not 50,000...
(Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
ORDER BY.)

> Page zip codes 31 - 40:
> select bottom 10 zip_code from (select top 40 zip_code from mytable where
> state_code='AZ' order by zip_code) as x
> Page zip codes 41 - 50:
> select bottom 10 zip_code from (select top 50 zip_code from mytable where
> state_code='AZ' order by zip_code) as x|||It should prove quite scalable, assuming that state_code and zip_code are
both indexed. Besides, any application that requires the user to page
through 50,000 records needs some serious re-engineering from a GUI
usability perspective. For example, when a user is browsing available
properties at a realestate website, then they are typically confined to
searching a specific zip code and price range. If they want a listing of all
properties on a regional basis, then that should be delivered in report form
or cached on the client side.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCCdOo0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The subquery solution is fine for 50 rows, but not 50,000...
> (Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
> ORDER BY.)
>
>
>
>