Unanswered: Applying an aggregate function on an in-memory result set based on each row in it
I need to generate a result set in two steps. The first step is simple and generates a base in-memory result set (let's call it B). In the second step, I need to add an additional column to B (let's call it c). For each row in B, c can be calculated by using an aggregate function on B based on the values of that row (this does look inefficient though).
The final result set is B + c. Results must be sorted by a column from B as well as the c column, so the whole thing should be a single query.
Here's a table analogy to illustrate what I mean by "applying an aggregate function on B based on each row in B":
Say, we have a table T with a column col. Let's say that for each row in T we want to calculate the number of rows that have a higher col value. You could do it like this:
(select sum(case when t2.col > t1.col then 1 else 0 end) from T t2) as count
from T t1
In my case the problem is that B is not a real table but an in-memory result set. How can I process it in a similar way?