Multiple use of RANK analytic in a single query

Using: 11.2.0.1 on RHEL5 64bit RAC cluster ...

I'm a novice in using the RANK analytical function, struggling with the basic syntax of it let alone trying to figure out if what I'm trying to achieve is possible, maybe even using subqueries ...

Data like this:

TX ID DEPT LOCATION LOAD

1    99    A        NY       12
2    99    A        LA       10
3    99    B        LA       05
4    77    B        LA       15
5    77    C        NY       12
6    77    D        LA       11

Desired result like this:

TX ID DEPT DEPT_RANK LOCATION LOC_RANK LOAD

1    99    A         1          NY         2        12
2    99    A         1          LA         1        10
3    99    B         2          LA         1        05
4    77    B         1          LA         1        15
5    77    C         2          NY         2        12
6    77    D         3          LA         1        11

DEPT_RANK for ID 99 is 1 for A because sum(LOAD) = 22 is the max LOC_RANK for ID 99 is 1 for LA because sum(LOAD) = 15 is the max ... etc

Is this actually possible, and if it is could anyone please assist with an example of the proper use of the WITHIN and/or PARTITION BY parts of RANK to achieve it?

TIA, Geoff M Received on Sun Nov 13 2011 - 18:53:21 CST