Running total aggregate column

[denimined]

Given a table of entities with an identifier, a bunch of attributes and a measure.
Identifier of: ID
Attributes of: Shape, Size, Color
And then the measure of: Weight

One entry in the table is one entity.
e.g. ID:35697 Shape:Round Size:10 Color:Blue Weight:12.345

From a table of 50,000 entries a sub-select is made that returns 1200 rows.
From there, I want to do one of two things.
1. From the 1200 items, pick the first 500 of them.
2. From the 1200 items, pick however many rows is required to get a maximum weight of 900.

I was thinking that if I added an aggregate column that is the running total of the weight, then the query is something like
MIN(RowNumber) - 1 where Weight > 900

So - how can I add an aggregate column.
Twisted wrinkle - the sub-select can be sorted by one of the attributes prior to the aggregate. For example, of the 1200 rows, sort by color - then select the rows.

Thanks for any assistance.

 

[Olaf Doschke]

In short look out for the new window functions.

SELECT a.weight, SUM(a.weight) OVER (ORDER BY a.color, a.shape,...) as runningtotalweight
FROM   YourTable a;

See

http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

To add your criteria of course you'd add WHERE runningtotalweight<=900 and then pick the top 1 in reverse order from there.

Bye, Olaf.

 

[Olaf Doschke]

A better article I referred to earlier already...

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

Bye, Olaf.