Get the dept_id from a sub query with the maximum date - ORACLE 11G


Dept ID               Department      Assignment_start_date  
ae0391                Linen                01/21/2009
593139                Jewelry            12/01/2011


Name              Dept_id       Stat
Jane Doe         a20391         98
Jane Doe         593139         88

I would like to  do the following
Select * from table1 where
      table1.dept_id = (select dept_id from table 2 with the maximum date)

I would like to link to  dept_id 593139
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try this:

select t1.*
from tab1 t1,
(select dept_id, max(assignment_start_date) from tab2 group by dept_id) t2
where t1.dept_id=t2.dept_id;
lwadwellConnect With a Mentor Commented:
Select a.* 
from table1 a
join table2 b on a.dept_id = b.dept_id
where b.Assignment_start_date = (select max(Assignment_start_date) from table2)

Open in new window

Select *
from (Select a.*, row_number() over(order by Assignment_start_date desc) rn
      from table1 a
      join table2 b on a.dept_id = b.dept_id)
where rn = 1

Open in new window

slightwv (䄆 Netminder) Commented:
That first one hits table2 twice.  I wouldn't consider that a valid option when other choices exist.

It would be interesting to get an explain plan for the second one versus mine on a medium sized table where dept_id is indexed.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

I assumed there was a typo between 'ae0391' and 'a20391' in table 2 and table2 and they were actually the expected to be the same value.  I also assumed the asker wanted the row with the max date for any dept_id.
Maybe I assumed wrong - perhaps @cookiejar will clarify.
HuaMin ChenConnect With a Mentor System AnalystCommented:
Select * from table1 where
      table1.dept_id = (select dept_id from table2 where Assignment_start_date in(select max(Assignment_start_date) from table2));
cookiejarAuthor Commented:
Yes  lwadwell, I want to get the maximum date for any dept_id and  yes there is a typo between 'ae0391' and 'a20391'
cookiejarAuthor Commented:
What I want to do to get the latest department the person was assigned to and this determined by the latest date.  In the example the last department the person was assigned to is  593139.
slightwv (䄆 Netminder) Commented:
Have you tried any of our queries?  I still beleive they will work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.