-2

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
Mayur
  • 11
  • 6
  • Duplicate of https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column or https://stackoverflow.com/questions/3491329/group-by-with-maxdate or https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time – MT0 May 03 '18 at 10:56

2 Answers2

0

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);
  • Thank you very much.. you have saved my day. looks like i am learning fast from your logic . great help thnx again Vivek. – Mayur May 03 '18 at 10:54
  • most welcome, I am happy to help you. –  May 03 '18 at 10:55
0

INNER JOIN may be used :

select e1.* 
  from employee_1 e1 
  inner join ( select mobile_no, max(e.start_date) start_date from employee_1 e group by mobile_no ) e2 
          on ( e1.mobile_no = e2.mobile_no and e1.start_date = e2.start_date )
  inner join mobile_1 m
          on ( m.mobile_no = e1.mobile_no );

SQLFiddle Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55