LeTay
asked on
Special counter query in SQL server 2005
I have to write a stored procedure which will get as parameter two numbers
The first is a value to "locate" in a table T1, the second is a kind of "step"
Here is a sample of my table (ordered ascending on C1)
T1
4
10
18
34
69
76
101
.../...
When the procedure receives, says value 37, and second parameter is 3, it must return the 3rd (3) value of the table, greater than 37
Is it possible in a single query or do I need a cursor and count lines ?
Single query is prefered solution
Thanks
The first is a value to "locate" in a table T1, the second is a kind of "step"
Here is a sample of my table (ordered ascending on C1)
T1
4
10
18
34
69
76
101
.../...
When the procedure receives, says value 37, and second parameter is 3, it must return the 3rd (3) value of the table, greater than 37
Is it possible in a single query or do I need a cursor and count lines ?
Single query is prefered solution
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wonderful result, but I do not understand it at all ...
did you try the second one? I think it will be faster.
row_number() just assign unique integer values starting at 1 and increasing by 1
the "partition by" is a bit like a "group by" this is a range over which the numbers will be assigned
order by determines where 1, 2, 3... is assigned
so the partition is all numbers > @val; or all numbers <= @val (i.e. 2 partitions)
order by says start at 1 for the number immediately after @Val
that number can be matched to @step
the second one is easier
row_number() just assign unique integer values starting at 1 and increasing by 1
the "partition by" is a bit like a "group by" this is a range over which the numbers will be assigned
order by determines where 1, 2, 3... is assigned
so the partition is all numbers > @val; or all numbers <= @val (i.e. 2 partitions)
order by says start at 1 for the number immediately after @Val
that number can be matched to @step
the second one is easier
select just the top number = to @step
where the C1 values > @val
ordered by C1
from that result reverse the order, so, now the last record becomes the first record
and just list that record by 'top 1'
where the C1 values > @val
ordered by C1
from that result reverse the order, so, now the last record becomes the first record
and just list that record by 'top 1'
ASKER
Sorry I missed it
Open in new window