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
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 formatPARENTID 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 SQLselect 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..
I also tried this Query. This is close, but not close enough to what I was expecting.
ReplyDeleteselect parentid,childid,val,rank() over (order by childid desc)
from temp_rec
order by childid desc
A solution that was received in one of the popular forum
ReplyDeleteSELECT 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)
A decode version
ReplyDeleteAnother 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