Anyway to emulate LIMIT Start,Count?

Does anyone know of a way to emulate the LIMIT clause that is available in Oracle and MySQL?

I could greatly improve my performance on my web application if I could figure out how to do it. I am using TOP right now to just pull back the records needed, but as with any page with many results, once you get 10 pages in you're pulling way too many records accross the wire.

The LIMIT clause works great in the other DBMS's, but MS SQL 2K does not have it. :(set rowcount 100|||That doesn't deal with the offset right??|||It just limits the number of rows returned to the client to 100 (or whatever number you specify)

Don't forget to set it to 0 to restore the default functionality - RETURN ALL ROWS.|||I already get that with TOP so there is no need for this.|||if you want LIMIT 30,20 (start at offset 30, i.e. 31st row, and return 20 rows) like this:

select *
from (
select top 20
foo
, bar
from (
select top 50
foo
, bar
from yourtable
order
by bar desc
)
order
by bar asc
)
order
by bar desc|||http://www.dbforums.com/t994143.html|||I like the multiple subselect idea but don't seem to be able to get it to work. The sql errors out when trying to do an order by on a subselect.

For example - this errors out:

select top 20 AccountID,[Name] from (
select top 50 AccountID,[Name]
from CRDACCOUNT
order by AccountID desc
) order by AccountID

Give an "Incorrect syntax near the keyword 'order'." on the last line.

Ideas?

PS: Thanks for the great replies so far everyone!!|||I'm 99 5/8% certain that you are just missing an alias, something like:SELECT TOP 20 AccountID, [Name]
FROM (SELECT TOP 50 AccountID, [Name]
FROM CRDACCOUNT
ORDER BY AccountID DESC) AS zz
ORDER BY AccountID-PatP|||wow, I just posted a question asking nearly the identical thing. So far it doesn't look good. My options are:

- Using an ORDER BY on column and maintain a bookmark position. This works but I can't get decent performance out of it.
- Paging techniques. Having trouble getting these to work right as well.

Let me know if you find an acceptable solution.|||Yep - it was the alias. Here is the first query formatted with the aliases:

select *
from (
select top 20
foo, bar
from (
select top 50
foo, bar
from yourtable
order
by bar desc
) as tbl1
order
by bar asc
) as tbl2
order
by bar desc

I guess the trick now is to figure out how to have multi fields in the ORDER BY clause.

Thanks for the help that has been given!