Multiple use of RANK analytic in a single query

[Geoff Muldoon]

Hi all,

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

[Geoff Muldoon]

geoff....@trap.gmail.com says...

Self-follow-up with what I've tried so far, this time with real-ish code
and data:

SELECT
LOAD_YEAR,
ORG_UNIT_CODE,
RANK () OVER (PARTITION BY LOAD_YEAR, CLASSIF_CODE
ORDER BY SUM (FTE_DAYS) DESC) ORG_RANK,
CLASSIF_CODE,
RANK () OVER (PARTITION BY LOAD_YEAR, ORG_UNIT_CODE
ORDER BY SUM (FTE_DAYS) DESC) CLASSIF_RANK,
SUM (FTE_DAYS) FTE
FROM STAFF_LOAD
GROUP BY LOAD_YEAR, ORG_UNIT_CODE, CLASSIF_CODE;

This gives me:

YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46 1 HEW3 1 59
2010 A42 2 HEW3 1 13
2010 A42 1 HEW4 1 13
2010 A46 2 HEW4 2 12

What I want is:

YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46 1 HEW3 1 59
2010 A42 2 HEW3 1 13
2010 A42 2 HEW4 2 13
2010 A46 1 HEW4 2 12

Geoff M

[Charles Hooper]

On Nov 13, 7:53 pm, Geoff Muldoon <geoff.muld...@trap.gmail.com>
wrote:

Geoff,

A sample table with test data would be helpful. For your first post
in this thread:
CREATE TABLE T1 (
TX NUMBER,
ID NUMBER,
DEPT VARCHAR2(1),
LOCATION VARCHAR2(2),
LOAD NUMBER);

INSERT INTO T1 VALUES (1,99,'A','NY',12);
INSERT INTO T1 VALUES (2,99,'A','LA',10);
INSERT INTO T1 VALUES (3,99,'B','LA',05);
INSERT INTO T1 VALUES (4,77,'B','LA',15);
INSERT INTO T1 VALUES (5,77,'C','NY',12);
INSERT INTO T1 VALUES (6,77,'D','LA',11);

I think that what needs to be done is to build the solution in
stages. First, let's calculate the SUM values for the rows - I took a
best guess at the partition clause using the raw data that you
provided and the expected output:
SELECT
TX,
ID,
DEPT,
SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
LOCATION,
SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
LOAD
FROM
T1
ORDER BY
TX;

TX ID D SUM_LOAD_ID LO SUM_LOAD_LOCATION LOAD
--- --- - ----------- -- ----------------- -----
1 99 A 22 NY 12 12
2 99 A 22 LA 15 10
3 99 B 5 LA 15 5
4 77 B 15 LA 26 15
5 77 C 12 NY 12 12
6 77 D 11 LA 26 11

Hopefully, you agree with the calculated SUM values shown above. If
we then take the above SQL statement and slide it into an inline view,
we are able to rank the values in the SUM_LOAD_ID and
SUM_LOAD_LOCATION columns. First, let's try something that does not
work:
SELECT
TX,
ID,
DEPT,
RANK() OVER (PARTITION BY ID,DEPT ORDER BY SUM_LOAD_ID DESC)
DEPT_RANK,
LOCATION,
RANK() OVER (PARTITION BY ID,DEPT ORDER BY SUM_LOAD_LOCATION DESC)
LOC_RANK,
LOAD
FROM
(SELECT
TX,
ID,
DEPT,
SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
LOCATION,
SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
LOAD
FROM
T1)
ORDER BY
TX;

TX ID D DEPT_RANK LO LOC_RANK LOAD
--- --- - ---------- -- ---------- -----

1 99 A 1 NY 2 12
2 99 A 1 LA 1 10

3 99 B 1 LA 1 5

4 77 B 1 LA 1 15

5 77 C 1 NY 1 12
6 77 D 1 LA 1 11

Quite clearly, the above did not work. We want to rank the
SUM_LOAD_ID and SUM_LOAD_LOCATION columns within each ID partition.
Let's try again:
SELECT
TX,
ID,
DEPT,
RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_ID DESC) DEPT_RANK,
LOCATION,
RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_LOCATION DESC)
LOC_RANK,
LOAD
FROM
(SELECT
TX,
ID,
DEPT,
SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
LOCATION,
SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
LOAD
FROM
T1)
ORDER BY
TX;

TX ID D DEPT_RANK LO LOC_RANK LOAD
--- --- - ---------- -- ---------- -----
1 99 A 1 NY 3 12

2 99 A 1 LA 1 10

3 99 B 3 LA 1 5

4 77 B 1 LA 1 15

5 77 C 2 NY 3 12

6 77 D 3 LA 1 11

The above is closer to the desired result - except that rows with
equal values in the SUM_LOAD_ID and SUM_LOAD_LOCATION columns cause a
ranking number to be skipped. If we change the RANK() function to a
ROW_NUMBER() function then no numbers will be skipped, but then rows
with equal values will be assigned a different rank. The DENSE_RANK()
analytic function should yield the desired results:
SELECT
TX,
ID,
DEPT,
DENSE_RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_ID DESC)
DEPT_RANK,
LOCATION,
DENSE_RANK() OVER (PARTITION BY ID ORDER BY SUM_LOAD_LOCATION DESC)
LOC_RANK,
LOAD
FROM
(SELECT
TX,
ID,
DEPT,
SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
LOCATION,
SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
LOAD
FROM
T1)
ORDER BY
TX;

TX ID D DEPT_RANK LO LOC_RANK LOAD
--- --- - ---------- -- ---------- -----

1 99 A 1 NY 2 12
2 99 A 1 LA 1 10

3 99 B 2 LA 1 5

4 77 B 1 LA 1 15
5 77 C 2 NY 2 12
6 77 D 3 LA 1 11

Are you able to provide a sample table with data for your real-ish
code? It is difficult to determine the original data values from the
two output examples that you provided.

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Geoff Muldoon]

hoope...@yahoo.com says...

> > 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 ...

> I think that what needs to be done is to build the solution in
> stages. First, let's calculate the SUM values for the rows - I took a
> best guess at the partition clause using the raw data that you
> provided and the expected output:

> SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,

> SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,

Eureka.

Thank you Charles, that partitioned summing in a subquery was just the
thing!

IOU big time.

> Are you able to provide a sample table with data for your real-ish
> code? It is difficult to determine the original data values from the
> two output examples that you provided.

Working example:

CREATE TABLE test_table (load_year NUMBER, person_id VARCHAR2(5),
org_code VARCHAR2(5), class_code VARCHAR2(5), load_value NUMBER);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '001', 'A64', 'HEW3', 55);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '001', 'A62', 'HEW3', 15);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '001', 'A62', 'HEW4', 15);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '001', 'A64', 'HEW4', 10);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '002', 'A52', 'HEW3', 20);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '002', 'A53', 'HEW4', 15);

INSERT INTO test_table (load_year, person_id, org_code, class_code,
load_value)
VALUES (2010, '002', 'A54', 'HEW4', 10);

SELECT
load_year,
person_id,
org_code,
DENSE_RANK() OVER (PARTITION BY load_year, person_id
ORDER BY org_load DESC)
AS org_rank,
class_code,
DENSE_RANK() OVER (PARTITION BY load_year, person_id
ORDER BY class_load DESC)
AS class_rank,
load_value
FROM
(SELECT
load_year,
person_id,
org_code,
SUM(load_value) OVER (PARTITION BY load_year, person_id, org_code)
AS org_load,
class_code,
SUM(load_value) OVER (PARTITION BY load_year, person_id, class_code)
AS class_load,
load_value
FROM test_table);

LOAD_YEAR PERSON_ID ORG_CODE ORG_RANK CLASS_CODE CLASS_RANK LOAD_VALUE
--------- --------- -------- ---------- ---------- ---------- ----------
2010 001 A64 1 HEW3 1 55
2010 001 A64 1 HEW4 2 10
2010 001 A62 2 HEW4 2 15
2010 001 A62 2 HEW3 1 15
2010 002 A52 1 HEW3 2 20
2010 002 A53 2 HEW4 1 15
2010 002 A54 3 HEW4 1 10

Perfecto!

Geoff