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:

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?