Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Special counter query in SQL server 2005

Posted on 2013-10-23
5
Medium Priority
?
303 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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 ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

715 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