Just to avoid confusion with my earlier post i am re-posting my requirement with new thread with sample input and outputs.
Table A has Registration_id and Client_No.
Table B has Last_Login_Email,Last_Login_Mobile and Last_Login_OFX timestamp of each Registration_id.
My requirement is below :-
Fetch all Registration_id's which are tied to multiple Client_no and out of these skip Registration_id with maximum time-stamp and fetch others.
So in above table AAA,BBB,CCC are tied to same Client_no which is 111.Also FFF,GGG tied to same CLient_no which is 666.
So these Registration_id (AAA,BBB,CCC) and (FFF,GGG) qualified for first part of my condition.
Now out of 1st set of Registration_id which are tied to same Client_no i want to skip the Registration_id with maximum time-stamp and fetch other.And same for 2nd set and so on.
Example: (for typing convinicence i have used Date instead of timestamp in below example)
Note:- In above table B we dont have to do anything with DDD and EEE as they are not qualified under 1st part of condition.I have given in above table just for sake of completeness.
Lets take 1st set here which is AAA,BBB,CCC
Code:
From Table B
Maximum-Timestamp out of Last_Login_Email,Last_Login_Mobile
andLast_Login_OFX
AAA 02/12/2017
BBB 03/01/2018
CCC 03/11/2017
Above we can see maximum timestamp is for BBB(out of AAA,BBB,CCC) so i want to skip BBB here and fetch AAA and CCC.
Same goes to other set which is FFF,GGG
Code:
From Table B
Maximum-Timestamp out of Last_Login_Email,Last_Login_Mobile and
Last_Login_OFX
FFF 14/01/2018
GGG 31/01/2018
Above we can see maximum timestamp is for GGG so i need to skip GGG and fetch FFF.
So my overall logic should fetch AAA,CCC and FFF.
Hope i am clear with my requiremet. Please reply incase of any doubts and sorry for such a lengthy post.
select temp3.Registration_id,temp3.Client_No
from tablea temp3
where temp3.Registration_id NOT IN (
select temp1.Registration_id from
(select temp.Registration_id,
temp.Client_No,
greatest((b.email_dt),(b.mobile_dt),(b.ofx_dt)) as max_dt
from
(select a.Registration_id,
a.Client_No
from tablea a
where a.Client_No IN (
select b.Client_No
from tablea b
group by b.Client_No
having count(b.Client_No ) >1)) as temp,
tableb b
where
temp.Registration_id = b.Registration_id)) as temp1) as temp2)
