SQL Server select rows between two values
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)
WITH mytable AS
(
SELECT *,
ROW_NUMBER() OVER (order by colname) AS 'RowNumber'
FROM table
)
SELECT *
FROM myTable
WHERE RowNumber BETWEEN 20 AND 60;
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