SQL - Selecting data between specific data rows

David Smith 21 Reputation points
2020-10-08T18:45:46.303+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-09T01:37:25.123+00:00

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2020-10-08T18:53:00.983+00:00

    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.

    0 comments No comments

  2. David Smith 21 Reputation points
    2020-10-08T19:11:24.523+00:00

    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.


  3. David Smith 21 Reputation points
    2020-10-09T02:39:46.817+00:00

    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