Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
comp.databases.oracle.misc
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion Multiple use of RANK analytic in a single query
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Geoff Muldoon  
View profile  
 More options Nov 15 2011, 5:49 pm
Newsgroups: comp.databases.oracle.misc
From: Geoff Muldoon <geoff.muld...@trap.gmail.com>
Date: Wed, 16 Nov 2011 09:49:54 +1100
Local: Tues, Nov 15 2011 5:49 pm
Subject: Re: Multiple use of RANK analytic in a single query
Print | View thread | Show original | Report this message | Find messages by this author
hooperc2...@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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google