How to get values against oldest date field in Oracle

I have a select query which take values from multiple tables with date as one of the fields:

select e.id,e.date,d.d_id
from emp e, d_source d
where e.d_id = d.d_id and e.emp_id = 100`


For each emp_id, there are 2 or more records. I have to select id from emp table against the oldest date value. There are cases when the date field values are null or date field values are same for all the selected records. In such case I have to select the ID which is associated with a particular d_id (say 123) of d_source table.

emp_id

id

emp

d_id

d_source

What I have tried so far is

`select e.id from emp e
where e.emp_id = 100
and e.date=(select min(date) from emp where emp_id = 100)`


These are some results of the first select query for 3 different emp_id-

Select results

So, I need to return only 'id' of the record having oldest date value and if date field values are equal or empty, I need to return 'id' of the record which have d_id as 456

Any help would be appreciated.

order by

d_source

1 Answer
1

Here's an example, based on what I understood.

SQL> -- sample data
SQL> with emp (emp_id, id, cdate, d_id) as
2 (select 100, 11111, date '2010-02-26', 123 from dual union all
3 select 100, 22222, date '2018-02-26', 456 from dual union all
4 --
5 select 200, 11122, date '2010-02-26', 123 from dual union all
6 select 200, 22211, date '2010-02-26', 456 from dual union all
7 --
8 select 300, 11133, null, 123 from dual union all
9 select 300, 22244, null, 456 from dual
10 ),
11 source (d_id) as
12 (select 123 from dual union all
13 select 456 from dual
14 )
15 -- query that, hopefully, returns result you need
16 select emp_id, id, cdate, d_id
17 from (select e.emp_id, e.id, e.cdate, d.d_id,
18 row_number() over (partition by e.emp_id order by e.cdate, d.d_id desc) rn
19 from emp e join source d on e.d_id = d.d_id
20 )
21 where rn = 1
22 order by emp_id;

EMP_ID ID CDATE D_ID
---------- ---------- ---------- ----------
100 11111 26/02/2010 123
200 22211 26/02/2010 456
300 22244 456

SQL>


WHERE e.emp_id = some_value

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.