Home » SQL & PL/SQL » SQL & PL/SQL » Co-related Subquery Throwing Error While Using rank function
| Co-related Subquery Throwing Error While Using rank function [message #677636] |
Tue, 01 October 2019 12:37  |
 |
chavva.kiru@gmail.com
Messages: 23
Registered: April 2012
Location: hyderabad
|
Junior Member |
|
|
Hi,
Create table A
(
a_id number,
a_first_clinical_owner varchar2(25),
a_policy_type varchar2(25)
)
create table B
(
b_id number,
b_a_pk varchar2(25)
)
create table C
(
c_id number,
c_a_pk number,
c_b_pk number,
trans_date date
)
insert into A(a_id,a_first_clinical_owner,a_policy_type) values(1654545,'MSclinician','Retail');
insert into B(b_id,b_a_pk)values(14636,1654545);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12181,1654545,14636,sysdate);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12182,1654545,14636,sysdate-1);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12183,1654545,14636,sysdate-2);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12184,1654545,14636,sysdate-3);
select c.C_ID,c.C_A_PK,c.C_B_PK, (select count(*) from (select rank() over(Order by trans_date desc) from C c1
where a.A_ID=c1.C_A_PK
and b.B_ID=c1.C_B_PK)where rn=1) rn from A a,B b,C c
where a.A_ID=B.B_A_PK
and a.A_ID=c.C_A_PK
and b.B_ID=c.C_B_PK
Throwing error as B.B_ID as Invalid Identifier.
How Can We rewrite The Query Such That I have to get O/P as
12181 1654545 14636 01-10-19 22:30
12182 1654545 14636 30-09-19 22:30
12183 1654545 14636 29-09-19 22:30
12184 1654545 14636 28-09-19 22:30
12185 1654545 14636 27-09-19 22:31
latest Record of 12185 to be displayed out of 2
|
|
|
|
Current Time: Wed Apr 24 09:57:52 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
