SQL Server limiting result set

[Ali Ekber]

I need to limit the result set coming from a SQL Server query. I can get the top 10 by SELECT TOP 10. But how do I get the rows from 11 to 20, for example? This is very easy in MySQL, but don't know the syntax for SQL server. Is this possible without stored procedures? Thanks.

[Amy Medrat]

Hey Ali, I hope this would help

select * from tableName
where rowid in (select rowid from tableName where rownum < 21
minus
select rowid from tableName where rownum< 11)

[Ali Ekber]

Amy, what are rowid and rownum? Are they keywords in SQL server? I am getting syntax error for them.

[Paul Sturrock]

No, but they are in Oracle. That query won't run in SQL Server.

[chien ajax]

SELECT TOP 10 * FROM
(SELECT TOP 30 * FROM dbo.JavaRanch ORDER BY ID )AS B
ORDER BY ID DESC

Get the rows from 21 to 30 in MSSQL

Perfect Version

SELECT * FROM (
    SELECT TOP 10 * FROM
        (SELECT TOP 30 * FROM dbo.JavaRanch ORDER BY ID )AS B
    ORDER BY ID DESC
    ) AS A
    ORDER BY ID ASC

[ March 10, 2006: Message edited by: chien ajax ]