How to return a range of rows in sql server

Hello,

John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me an
idea how to achieve paging in sql server without row number
functionality. Thank you John. The following works for me, not very
eficient though:

SELECT * FROM
( SELECT top 5 * FROM
( SELECT top 10 * FROM
( SELECT top 10 *
FROM dft_document
ORDER BY documentkey ASC
) a
ORDER BY documentkey DESC
) b
) d
ORDER BY documentKey ASC

The innermost SELECT gives 10 rows out of which last 5 needed.

regardsSeveral alternative methods described here:

www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--|||"Alex Ravvin" <alexravv@.hotmail.com> wrote in message
news:f497c8a4.0401291050.175bcef0@.posting.google.c om...
> Hello,
> John Bell posted a reply on 2003-11-02 04:11:02 PST, that gave me an
> idea how to achieve paging in sql server without row number
> functionality. Thank you John. The following works for me, not very
> eficient though:
> SELECT * FROM
> ( SELECT top 5 * FROM
> ( SELECT top 10 * FROM
> ( SELECT top 10 *
> FROM dft_document
> ORDER BY documentkey ASC
> ) a
> ORDER BY documentkey DESC
> ) b
> ) d
> ORDER BY documentKey ASC
> The innermost SELECT gives 10 rows out of which last 5 needed.
> regards

You can simplify this to

SELECT *
FROM (SELECT TOP 5 *
FROM (SELECT TOP 10 *
FROM dft_document
ORDER BY documentkey ASC) AS T10
ORDER BY documentkey DESC) AS T5
ORDER BY documentkey ASC

Regards,
jag