Row-by-row addition to a limit
I have a table containing the fields Stand, Species, Diameter, TreesPerAcre. I need to retrieve the rows from each Stand where the TreesPerAcre sum to between 20 and 25.
In the following subset only the first five rows should return.
Stand Species Diameter TreesPerAcre
101 DF 48 2
101 NF 27 3
101 DF 24 10
101 DF 22 6
101 PP 18 3
101 DF 16 5
The problem is getting a row-by-row addition to a limit. Using HAVING SUM(TreesPerAcre) BETWEEN 20 AND 25
doesn't work because SUM(TreesPerAcre) for Stand 101 = 29. I surely hope someone has a clue for me.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 25, 2005 2:10 PM
UPDATED:
October 26, 2005 2:09 PM
Answer Wiki:
I guess I'm not understanding the question. You say you only want retrieve the rows if the sum is between 20 and 25, which means your query SHOULDN'T return the rows for Stand 101, because they add up to 29. 29 is clearly outside your range, but you seem to be saying you think those rows should be returned. What am I missing?
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for the reply. You’re not missing anything, I didn,t say it clearly. I need to retrieve the first 5 rows by adding TreesPerAcre row-by-row until the total is between 20 and 25.
This is actual data from a forest inventory and the manager needs the first set of rows from each stand where the total TreesPerAcre is between 20 and 25. The number of rows returned will vary from one stand to another.
There are about 500,000 rows to process. I am working on a WHILE but have not got it yet. Hope you can help.