CODEWITHSUNDEEP
sqlsql-server
neolaser
neolaser

Reputation: 6907

SQL Server select rows between two values

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

Upvotes: 7

Views: 36659

Answers (7)

Antti A
Antti A

Reputation: 500

To get rows 20-60 you can use LIMIT :

SELECT * FROM table limit 20, 40

Upvotes: 0

Nitika Chopra
Nitika Chopra

Reputation: 1405

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.

Upvotes: 0

Kuldeep Singh
Kuldeep Singh

Reputation: 1

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>

Upvotes: 0

MikeTeeVee
MikeTeeVee

Reputation: 19422

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

Upvotes: 4

pcofre
pcofre

Reputation: 4076

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

Upvotes: 2

Martin Booth
Martin Booth

Reputation: 8595

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)

Upvotes: 10

Hogan
Hogan

Reputation: 70528

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

Upvotes: 7

Related Questions