SQL query - find row which exceeds cumulative proportion

Question

Say I have a table of data that looks like:

ItemNo    |    ItemCount   |    Proportion
------------------------------------------
1              3                0.15 
2              2                0.10
3              3                0.15
4              0                0.00
5              2                0.10
6              1                0.05
7              5                0.25
8              4                0.20

In other words, there are a total of 20 items, and the cumulative proportion of each ItemNo sums to 100%. The ordering of the table rows is important here.

Is it possible to perform a SQL query without loops or cursors to return the first ItemNo which exceeds a cumulative proportion?

In other words if the 'proportion' I wanted to check was 35%, the first row which exceeds that is ItemNo 3, because 0.15 + 0.10 + 0.15 = 0.40

Similarly, if I wanted to find the first row which exceeded 75%, that would be ItemNo 7, as the sum of all Proportion up until that row is less than 0.75.

Answer 1

select top 1
  t1.ItemNo
from
  MyTable t1
where
  ((select sum(t2.Proportion) from MyTable t2 where t2.ItemNo <= t1.ItemNo) >= 0.35)
order by
  t1.ItemNo

Answer 2

A classic for a window function:

SELECT * 
FROM   (
    SELECT ItemNo
          ,ItemCount
          ,sum(Proportion) OVER (ORDER BY ItemNo) AS running_sum
    FROM   tbl) y
WHERE  running_sum > 0.35
LIMIT  1;

Works in PostgreSQL, among others.

Or, in tSQL notation (which you seem to use):
SELECT TOP 1 *
FROM (
SELECT ItemNo
,ItemCount
,sum(Proportion) OVER (ORDER BY ItemNo) AS running_sum
FROM tbl) y
WHERE running_sum > 0.35;

Doesn't work in tSQL as commented below.