SQL Select query that returns a range [duplicate]

This question already has answers here: Closed 11 years ago.

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?

---

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

---

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

---

In mysql this is

SELECT * FROM table LIMIT 10,20

---

What DB are you using? If you're on Oracle, try

where rownum >= 10 and rownum < 20;