SQL - Selecting data between specific data rows

Question

How do I select specific data rows from a table?

Example: Table_A has 1,000,000 data rows and data columns VAR_1, VAR_2, VAR_3.

I want to select all data between data rows 100,000 and 125,000

Answer 1

Hi @David Smith ,

As mentioned by Scott, you need to have a column to determine the start position.

You could refer below two methods:

First one, add one column as ID and define it as primary key and identity(1,1). Then you could have ID as 1,2,3,...,1000000.

Try with below:

select * from Table_A where ID between 100000 and 125000   
  
select *  
 from Table_A  
 order by ID  
 offset 100000 rows  
 fetch next 125000-100000+1 rows only  

Second one, define the order of columns VAR_1, VAR_2, VAR_3. For example, VAR_1 is most important and VAR_3 is the least important.

Try with below:

select *  
from Table_A  
order by VAR_1, VAR_2, VAR_3  
offset 100000 rows  
fetch next 125000-100000+1 rows only  
  
select * from   
(select Row_Number() over   
(order by VAR_1, VAR_2, VAR_3) as RowIndex, * from Table_A) as Sub  
Where Sub.RowIndex >= 100000 and Sub.RowIndex <= 125000  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try something like this:

select *
from Table_A
order by VAR_1
offset 100000 rows
fetch next 125000-100000+1 rows only

You must specify 'order by' some column or columns.

Show the SQL version, if it does not work.

Answer 3

Thx. I'm using SQL Server Management Studio V17.8.1 and your code works. However, the data is not sorted, and looks like I need to use an ORDER BY, but I want to pull out the selected data rows as loaded into SQL. I tried to use ORDER BY ROW_NUMBER() and other variants with OVER() and no luck.

Answer 4

Thx to all who responded to my request - below is a variant on Melissa's code where I use a "select null" - and the code runs as intended:

SELECT *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum, *
FROM TABLE_A) SubQuery
WHERE SubQuery.RowNum BETWEEN 100000 AND 125000

Regards,
David