SQL Query dynamically retrive based on Key Record

Madhu P 21 Reputation points
2021-02-22T09:23:07.007+00:00

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
Transact-SQL
Transact-SQL
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
    2021-02-22T09:37:51.42+00:00

    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
    2021-02-22T10:18:06.157+00:00

    based max ( Run) by using inside query?


  3. MelissaMa-MSFT 24,176 Reputation points
    2021-02-23T06:50:04.293+00:00

    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  
    ('P',1,20,null),  
    ('A',1,10,null),  
    ('S',1,20,1),  
    ('P',1,20,null),  
    ('A',1,10,null),  
    ('S',1,20,2),  
    ('A',1,10,null),  
    ('S',1,20,3)  
      
    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)  
    

    Output:

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

    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.