Solved

# Special counter query in SQL server 2005

Posted on 2013-10-23
295 Views
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
0
Question by:LeTay
• 3
• 2

LVL 48

Accepted Solution

PortletPaul earned 500 total points
This returns c1=101 with inputs of 37 and 3:
``````DECLARE @Val int, @Step int

SET @Val = 37
SET @Step = 3

SELECT
c1
FROM (
SELECT c1, row_number() over (PARTITION BY CASE WHEN c1 > @Val THEN 0 ELSE 1 END
ORDER BY c1) AS rn
FROM t1
) AS derived
WHERE c1 > @Val
AND rn = @Step
;
``````
0

LVL 48

Expert Comment

this might be better:
``````DECLARE @Val int, @Step int

SET @Val = 37
SET @Step = 3

SELECT TOP 1
c1
FROM (
SELECT top (select @step)
c1
FROM t1
WHERE c1 > @Val
ORDER BY C1
) AS derived
ORDER BY
C1 DESC
;
``````
0

Author Closing Comment

Wonderful result, but I do not understand it at all ...
0

LVL 48

Expert Comment

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

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'
0

Author Comment

Sorry I missed it
0

## Featured Post

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.