Return rows between a specific range, with one select statement

Question

I'm looking to some expresion like this (using SQL Server 2008)

SELECT TOP 10 columName FROM tableName

But instead of that I need the values between 10 and 20. And I wonder if there is a way of doing it using only one SELECT statement.

For example this is useless:

SELECT columName FROM
(SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName) AS alias
WHERE RowNum BETWEEN 10 AND 20

Because the select inside brackets is already returning all the results, and I'm looking to avoid that, due to performance.

Answer 1

There is a trick with row_number that does not involve sorting all the rows.

Try this:

SELECT columName
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (select NULL as noorder)) AS RowNum, *
      FROM tableName
     ) as alias
WHERE RowNum BETWEEN 10 AND 20

You cannot use a constant in the order by. However, you can use an expression that evaluates to a constant. SQL Server recognizes this and just returns the rows as encountered, properly enumerated.

Answer 2

Use SQL Server 2012 to fetch/skip!

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

There's nothing better than you're describing for older versions of sql server. Maybe use CTE, but unlikely to make a difference.

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE 
    RowNumber BETWEEN @From AND @To  

or, you can remove top 10 rows and then get next 10 rows, but I double anyone would want to do that.