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
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)
SELECT * FROM ( <above code> ) t2 ORDER BY xx ASC.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
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
Select * from (Select row_number() over(order by Column_name) as Num ,Col_name1,Col_name2,Col_name3 from table_name) Table_name where Num>5 and Num<10;
For Example:
Select * from emp;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> Select * from (Select row_number() over(order by empno) as Num,ename,empno,deptno,sal from emp) emp where Num>5 and Num<10;
NUM ENAME EMPNO DEPTNO SAL
6 BLAKE 7698 30 2850
7 CLARK 7782 10 2450
8 SCOTT 7788 20 3000
9 KING 7839 10 5000
SQL>
This query may help you:
select * from tablename order by columnname offset 20 rows fetch next 40 rows only
It treats 21st row as 1st row and fetches next 40 rows from a 21st row.
To get rows 20-60 you can use LIMIT :
SELECT * FROM table limit 20, 40