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_tx_setting)values(14636,'HCSCTSO');
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);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12185,1654545,14636,sysdate- 4);


I want to get a o/p
after Joining a,b,c tables with total no of Latest_record from table C.

Actually My requirement is to count the closed_tickets for a Particular Person.

I Used Co related subquery to get the latest record by using rank Function and Iam getting error as b.B_ID not found.
what is the other way to write the query?

select a.A_ID,c.C_ID,(select * from (select rank() over(order by c.trans_date desc) rn
from C c where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID) where rn=1)
Closed_ticket_count from A a,B b,C c
where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID
/