sql - Get order number that makes the total sum of orders be 1000 -

i have table of orders, , each row of have column called price. each of orders has column called created_at when order created.

what way find out order make total amount of prices pass $1000?

so, imagine have 3 orders this:

order 1: price: $800 - created_at: 2013/07/11   order 2: price: $100 - created_at: 2013/07/13   order 3: price: $300 - created_at: 2013/07/14  

i interested in finding order 3 1 made me pass on $1000, because if add $800 + $100 + $300, $300 made total amount bigger $1000.

what query perform find that?

after calculating running sum window aggregate function sum(), pick first row according created_at exceeds 1000:

select * (    select order_id, created_at         , sum(price) on (order created_at) sum_price      orders    ) sub  sum_price >= 1000 order  created_at  limit  1; 

this should faster @gordon's version, because picking first according same order that's used in window function lot cheaper calculating value every row, not sargable.

i use sum_price >= 1000, reaching 1000 qualifies, too. if exceeding should qualify use > instead of >=.

the manual on window functions informs:

in addition these functions, built-in or user-defined aggregate function can used window function

it should noted, query delivers 1 row, opposed @gordon's query. in case multiple rows identical created_at cross 1000 barrier, of them qualify in gordon's answer (or fail, see below), while one picked in mine. arbitrary one, long don't add more items order by tiebreaker. like:

order created_at, order_id 

there 2 instances of order in query, , happens modify either or both make work. both make sort order identical, should fastest.

actually, gordon's version fail completely test case:

create temp table orders(order_id int, price int, created_at date);  insert orders values   (1, 500, '2013-07-01')  ,(2, 400, '2013-07-02')  ,(3, 100, '2013-07-03')  ,(4, 100, '2013-07-03')  ,(5, 100, '2013-07-03'); 

you fix making sort order in window function unique demonstrated above.

or change frame definition window function to:

rows between unbounded preceding , current row 

read fine print in manual.

but it's slower either way.

-> sqlfiddle