Applying an aggregate function on an in-memory result set based on each row in it

I nee开发者_JAVA技巧d 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.

Getting B is simple, but how do I process it further based on its contents?

EDIT. 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
  t1.*, 
  (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?

OK - I'll give it another go.

Aliasing both the in memory results set and the columns that you need to use in that results set to derive c should work.

To continue your example from above:

select B.col,
(select sum(case when B2.col > B.col then 1 else 0 end) from (select whatever_col as col
from whatever_table) B2) as c
from
(select whatever_col as col from whatever_table) as B;

Let me know if I've got the wrong end of the stick again!