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 Go to previous message
chavva.kiru@gmail.com
Messages: 22
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


 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: running total query
Next Topic: Oracle 11g: step by step guide to send email via utl_smtp with all requirements from scratch (3 merged)
Goto Forum:
  


Current Time: Tue Sep 22 19:54:19 CDT 2020