SQL Query dynamically retrive based on Key Record

Question

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

Answer 1

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

Answer 2

based max ( Run) by using inside query?

Answer 3

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.