Limiting Result for Pagination

Hello,

We are migrating from VB6/MySQL to VB6/MS SQL.

The application retrieves limited records for paginated results.
This way we can access large databases, but only return rows for one screen.

For example:
Select row 1 - 10, then 11 - 20 then 21 to 30 etc.

With MySQL we can use LIMIT and OFFSET and it works great.
MySQL
SELECT <columns> FROM <tables> [WHERE <condition>] [ORDER BY <columns>]
[LIMIT [offset,]howmany>]

Does anybody know how we can do something similar with MS SQL?

Any feedback is appreciated!

Thank you,

Edi

Hello cell-gfx!

I found basically the same. It works fine.

Thanks for your Help

Edi

SELECT * FROM (
SELECT TOP x * FROM ( -- (x = LinesPerPage
SELECT TOP z ProductID -- (z = CurrentPage * LinesPerPage)
FROM Products
ORDER BY ProductID ASC
) AS foo ORDER BY ProductID DESC -- ('foo' may be anything "One
To Current Page)
) AS baz -- ('baz' may be anything "Current
Page Reverse Order")
ORDER BY ProductID ASC -- ("Current Page in Order"