Solved

Special counter query in SQL server 2005

Posted on 2013-10-23
5
299 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

756 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