CODEWITHSUNDEEP
sqlsql-servert-sql
Prakash
Prakash

Reputation: 555

T-SQL Query to get ending position in a result set

I have a result set to which I will provide an input position and a number for iteration. I expect a result of the end position.

-------------
ID        
-------------
1
2
3
4
5
6
7

--------------------------------
InputPosition     Iteration
--------------------------------
4                 6

----------------
ID   Iteration   
----------------
1    5           
2    6 (EndPosition = 2)      
3 
4    1  
5    2
6    3
7    4

Hence I need to get the 'EndPosition' for this scenario.

Upvotes: 2

Views: 304

Answers (2)

S3S
S3S

Reputation: 25132

This will work only for your sequential number set.

declare @table table (id int)
insert into @table (id)
values
(1),
(2),
(3),
(4),
(5),
(6),
(7)

declare @inputPosition int = 4
declare @iteration int = 6

;with cte as(
select 
    id,
    row_number() over (order by case when id = @inputPosition then 1 else @inputPosition +1 end) as rn
from @table)

select
    *
from
    cte
where rn = @iteration

Upvotes: 1

Steve Lovell
Steve Lovell

Reputation: 2564

I'm not sure how important the tables are to the task at hand. If the answer is not very, the following may work for you:

declare @input as int = 4
declare @itemCount as int = 7
declare @iterations as int = 6

select
    case when (@input + @iterations - 1) % @itemCount = 0 then 7
    else (@input + @iterations - 1) % @itemCount
end as EndPosition

If the tables are important, then you may be able to use this logic in combination with the row_number() function.

Upvotes: 1

Related Questions