Analytic Functions - Ranking in Desired Order

A new problem for which I've not found the solution till now..The objective would be achieve the result in a single SQL statement for the problem that I'm listing below:

The table that I have has the following DDL


CREATE TABLE TEMP_REC
(
PARENTID  VARCHAR2(60 BYTE)                   NOT NULL,
CHILDID   VARCHAR2(60 BYTE)                   NOT NULL,
VAL       VARCHAR2(762 BYTE)                  NOT NULL
)
Some preparatory data for this table in the form of insert statements:


Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '78056359', 'Z1');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '64850981', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62103647', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102722', 'E3');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102567', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102433', 'E3');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102275', 'Z1');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102115', 'E3');
Now, my objective is to write a single SQL to get the output in the following format


PARENTID CHILDID VAL  RANK

1 78056359 Z1    1
1 64850981 F8    2
1 62103647 F8    2
1 62102722 E3    3
1 62102567 F8    4
1 62102433 E3    5
1 62102275 Z1    6
1 62102115 E3    7
The catch here, is that the rank should be same as long "VAL" values get repeated. So, the second row and third row with VAL of "F8" gets a rank of 2. This is fine. But the fifth row containing a value of "F8" should get a rank of 4. When I try the following SQL


select parentid,childid,val,dense_rank()over (partition by val order by childid desc)
from temp_rec
order by childid desc
I get the following output,


1 78056359 Z1 1
1 64850981 F8 1
1 62103647 F8 2
1 62102722 E3 1
1 62102567 F8 3
1 62102433 E3 2
1 62102275 Z1 2
1 62102115 E3 3

But, this is not what I'm looking for. I'm not sure if RANK and DENSE RANK analytic functions would help my cause here. I'm not interested in PL SQL based approach to solve this..if somebody can throw some ideas, they are most welcome..

3 comments:

  1. I also tried this Query. This is close, but not close enough to what I was expecting.

    select parentid,childid,val,rank() over (order by childid desc)
    from temp_rec
    order by childid desc

    ReplyDelete
  2. A solution that was received in one of the popular forum

    SELECT parentid,
    childid,
    val,
    Sum(Decode(x,val,0,
    1))
    OVER(PARTITION BY parentid ORDER BY childid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) abcd
    FROM (SELECT parentid,
    childid,
    val,
    Lag(val)
    OVER(PARTITION BY parentid ORDER BY childid DESC) x
    FROM temp_rec)

    ReplyDelete
  3. A decode version

    Another solution using Decode,

    with
    flagged as (
    select parentid, childid, val,
    Decode(nvl(lag(val) over (partition by parentid order by childid desc),'#'),val,NULL,1)
    as flag
    from temp_rec
    )
    select parentid, childid, val,
    count(flag) over (partition by parentid order by childid desc) rk
    from flagged

    ReplyDelete