SQL Query dynamically retrive based on Key Record

Madhu P 21 Reputation points

My Table like below

ID  Name     Pract    score     Run
---  -------    --------    ----    -----
1     P           1           20          null
2     A           1          10          null
3     S           1           20          1 
4     P           1           20          null
5     A           1          10          null
6     S           1           20          2 
7    A           1          10          null
8     S           1           20          3

those records dynamically inserting//.
Now I Need the query based on RUN,
to pick the if Run = 1 before there are Two IDs(1,2) available in table So need this records (1,2 IDs) only &
if if Run = 2 before there are 5 IDs(1,2,3,4,5) available in table So need this records(4,5 IDs)
if if Run = 3 before there are 5 IDs(1,2,3,4,5,6,7) available in table So need this records(7 ID) ..... like dynamically data
so need get Query dynamically

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,047 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 113.7K Reputation points

    Try one of solutions:

    declare @run int = 2 -- example of Run
    select *
    from MyTable
    where ID < ( select ID from MyTable where Run = @run)
    and ( ID > ( select ID from MyTable where Run = @run - 1) or @run = 1 )
    order by ID
    0 comments No comments

  2. Madhu P 21 Reputation points

    based max ( Run) by using inside query?

  3. MelissaMa-MSFT 24,176 Reputation points

    Hi @Madhu P ,

    Welcome to Microsoft Q&A!

    You could refer Viorel's answer which will return the expected ids while Run is defined as 1,2,3 or other integer.

    Please also refer below which could return all results in one query.

    create table #Run  
    ID int identity(1,1),  
    Name char(1),  
    Pract int,  
    Score int,  
    Run int  
    insert into #run (name,pract,Score,run) values  
    select a.id,b.run from   
    (select * from #run where run is null) a   
    ,(select *,lag(id) over (order by id) lag from #run where run is not null) b   
    where a.id<b.id and (a.id>b.lag or b.lag is null)  


    id	run  
    1	1  
    2	1  
    4	2  
    5	2  
    7	3  

    Best regards

    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.