Return rows between a specific range, with one select statement

database, sql, sql-server, sql-server-2008

Solution

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.

Problem

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.

Original source