filter on a nested select.
select id, ( select sum(i2.size) from items i2 where i2.id <= i1.id ) sum_size from items i1 where sum_size < 5; I'm sure performance sucks :-) Something like this would be much faster to do in a procedural language instead of in SQL. HTH, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: Steve Krulewitz [mailto:[EMAIL PROTECTED] Sent: Monday, May 14, 2007 8:26 PM To: sqlite-users@sqlite.org Subject: [sqlite] Limit selection by rolling sum? Hey all -- I have a table that I would like to select the first N records where the rolling sum of a given column is less than some literal value. SQL for this might look like: select item_id from items where rolling_sum(item_size) < 10000 order by item_name; Is there a way to do this? Would sqlite know to stop processing the query once the limit is reached? Manually doing the rolling sum while reading the query result is not a good option for me as I am working with an API on top of sqlite that does not allow this. I am already using a custom collation sequence, so adding a custom function would not be a problem :) cheers, -steve ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------