SQL Select query that returns a range [duplicate]

Question

Possible Duplicate:
Row Offset in MS SQL Server

I want to select a range from x1 to x2. Just like you can select the top results:

SELECT TOP X * FROM TABLE

SELECT TOP 5 * FROM tUsers

But I would like to select middle results. So if I want results 10-20 is there a way to query that?

SELECT 10-20 * FROM TABLE?

Answer 1

With SQL Server :

Row Offset in SQL Server

With MySQL :

SELECT * FROM `your_table` LIMIT 10, 20

With Oracle :

SELECT * FROM `your_table` WHERE rownum >= 10 and rownum < 20;

With PostgreSQL :

SELECT * FROM `your_table` LIMIT 20 OFFSET 10

`your_table` must be replaced by your real table name

Answer 2

In SQL Server 2005 or above you can use a CTE and the ROW_NUMBER function:

WITH TblCte as
(
SELECT  *
        ,ROW_NUMBER() OVER (ORDER BY OrderCol) RowNumber
FROM    Table
)
SELECT  *
FROM    TblCte
WHERE   RowNumber between 10 and 20

In SQL Server 2000 or below, it was quite difficult and inefficient: http://social.msdn.microsoft.com/Forums/en-IE/transactsql/thread/e92d9b03-42ad-4ab9-9211-54215e7b9352