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
7条答案
按热度按时间hiz5n14c1#
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)
6rvt4ljy2#
1wnzp6jl3#
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.
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
j2cgzkjk4#
In previous versions of SQL, an option is to use a temporary table:
xxb16uws5#
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;
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;
SQL>
mlmc2os56#
This query may help you:
It treats 21st row as 1st row and fetches next 40 rows from a 21st row.
6ie5vjzr7#
To get rows 20-60 you can use LIMIT :