Solved

Special counter query in SQL server 2005

Posted on 2013-10-23
5
295 Views
Last Modified: 2013-10-23
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
Comment
Question by:LeTay
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
;

Open in new window

0
 

Author Closing Comment

by:LeTay
Comment Utility
Wonderful result, but I do not understand it at all ...
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

by:LeTay
Comment Utility
Sorry I missed it
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now