Oracle query aggregate function

Question

I have 2 tables name employee_1 and mobile_1 in oracle empolyee_1 data is

  Mobile_No  Sim_No      Start_Date         End_Date 
1111111113  1111112222  11/10/2017 21:02:44 13/10/2017 21:02:44
1111111111  1212121212  3/10/2017 21:02:44  10/10/2017 21:02:44
1111111111  1111111111  11/10/2017 21:02:44 13/10/2017 21:02:44
1111111111  1111111112  11/10/2017 21:02:44 13/10/2017 21:02:44
1111111111  1111111111  12/10/2017 21:02:44 

Mobile_1 data is

  Mobile_No  Status
1111111111  connected
1111111112  connected

I want to retrieve all the 4 columns of first table. Condition is empolyee_1.mobile_No = Mobile_1.mobile_no with maximum start date.

Answer 1

Here is the solution, please check it out...

select mobile_no,sim_no,start_date,end_date from(
select mobile_no,sim_no,start_date,end_date,rank() over(partition by mobile_no,sim_no order by start_date desc) rn from employee_1)s
where rn=1
and exists (select 1 from mobile_1 m where m.Mobile_No = s.mobile_no);