SQL Server select rows between two values

Question

Hopefully the title explains it enough, but I want to be able to select rows in an SQL Server table between two values

example

SELECT * FROM table WHERE rows between 20 and 60

I have tried the ROW_NUMBER() and then use a WHERE clause....

Thanks,

Jason

Answer 1

SELECT TOP 40 * FROM (SELECT TOP 60 * FROM table ORDER BY xx ASC) t ORDER BY xx DESC

Since the output of a select statement can return records in any order (without an order by clause) you need to decide which order to apply to the records... use the primary key if you don't know or care (substitute for xx)

Answer 2

WITH mytable AS
(
   SELECT *,
    ROW_NUMBER() OVER (order by colname) AS 'RowNumber'
    FROM table
) 
SELECT * 
FROM myTable 
WHERE RowNumber BETWEEN 20 AND 60;

Answer 3

If you have SQL Server 2012 (or higher) you may use Offset-Fetch for this.
See this Microsoft Technet Article on the Offset-Fetch Clause.
You will need to specify an Order-By (which I think is obvious).

If you want Rows Between 20 and 60, then what you're really saying is you want to start at 20 (your Offset) and then select (or Fetch) the next 40.

SELECT *
  FROM TableName
 ORDER BY SomeColumnName
 OFFSET 20 ROWS
 FETCH NEXT 40 ROWS ONLY

You can even use Variables and Calculations for your Fetch and Offset values.
Here's an example for exactly what the question asks for: Rows between 20 and 60

DECLARE @RowStart Int = 20
DECLARE @RowEnd   Int = 60
SELECT *
  FROM TableName
 ORDER BY SomeColumnName
 OFFSET @RowStart ROWS
 FETCH NEXT (@RowEnd - @RowStart) ROWS ONLY

Answer 4

In previous versions of SQL, an option is to use a temporary table:

SELECT  IDENTITY(int,1,1) RowNumber,* 
into    #Temp
FROM    Table1

SELECT  *
FROM    #Temp
WHERE   RowNumber between 20 and 60