Solved

Special counter query in SQL server 2005

Posted on 2013-10-23
5
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39593543
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
ID: 39593560
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
ID: 39593928
Wonderful result, but I do not understand it at all ...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39593963
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
ID: 39593978
Sorry I missed it
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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