?
Solved

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

Posted on 2012-08-13
8
Medium Priority
?
705 Views
Last Modified: 2012-08-16
Table2

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

Table1

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
0
Comment
Question by:cookiejar
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 668 total points
ID: 38290348
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;
0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 664 total points
ID: 38290406
Try:
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

or
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

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38290449
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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 25

Expert Comment

by:lwadwell
ID: 38290565
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.
0
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 668 total points
ID: 38291295
Try
Select * from table1 where
      table1.dept_id = (select dept_id from table2 where Assignment_start_date in(select max(Assignment_start_date) from table2));
0
 

Author Comment

by:cookiejar
ID: 38292451
Yes  lwadwell, I want to get the maximum date for any dept_id and  yes there is a typo between 'ae0391' and 'a20391'
0
 

Author Comment

by:cookiejar
ID: 38292466
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38292650
Have you tried any of our queries?  I still beleive they will work.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month17 days, 1 hour left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question