Oracle SQL - one to many join, use of max

Posted on 2011-03-02
Last Modified: 2012-05-11

In an Oracle 11 databaseI have three tables.  The main_table has a structure as follows:

      rec_key      CHAR(20)
      tab1e1_data1 CHAR(10)
      table1_data2 CHAR(10)

and contains the following data:

      01234567890000100001      first_line_1      first_line_2
      01234567890000200001      first_line_1      first_line_2

The second_table, which contains details of record edits,  has the following structure:

      rec_key          CHAR(15)
      date             CHAR(8)
      hour             CHAR(4)
      who_rec_key  CHAR(10)

and contains data as follows:

      012345678900001            20110302      0830      0000000001
      012345678900001            20110301      1446      0000000002
      012345678900002            20110228      1030      0000000001
      012345678900002            20110302      1530      0000000002

The join between the main_table and the second_table is:

      substr(main_table.rec_key,1,15) = second_table.rec_key
My third_table, which contains more info about the "who", has the following structure:

      who_rec_key  CHAR(10)
      name         CHAR(25)
and contains data as follows:

      0000000001            DOE,JOHN
      0000000002            SMITH,JOHN
The join between the second_table and the third_table is:

      second_table.who_rec_key = third_table.who_rec_key
Using the data on each of these -  I want to be able to retrieve each line from the main_table, and the ID and name of the person who was last to edit this record.  Given the data above, I want the query to result in:

      01234567890000100001      first_line_1      first_line_2      0000000001      DOE,JOHN
      01234567890000200001      first_line_1      first_line_2      0000000002      SMITH,JOHN
How do I achieve this with the required joins and needing to use a max statement based on the contents of the two field (date, hour) that give me the "timestamp" of the record edits?

Question by:yelbow
LVL 40

Accepted Solution

Sharath earned 500 total points
ID: 35015121
try this.
SELECT t1.rec_key, 
  FROM main_table AS t1 
       JOIN (SELECT rec_key, 
                      OVER(PARTITION BY rec_key ORDER BY DATE DESC) AS rn 
               FROM second_table) AS t2 
         ON SUBSTR(t1.rec_key,1,15) = t2.rec_key 
       JOIN third_table AS t3 
         ON t2.who_rec_key = t3.who_rec_key 
 WHERE t2.rn = 1

Open in new window

LVL 28

Expert Comment

by:Naveen Kumar
ID: 35015156
there are many ways to do this.. just one way is here.

try this :

select (
select a.rec_key  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) rec_key,
(select a.table1_data1  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) table1_data1,
(select a.table1_data2  from first_table a, second_table b, third_table c
where  substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key and rownum = 1) table1_data2,    
from third_table c
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35015169
I have written an article about this kind of scenarios:
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35015174
another method and better than the earlier is below.. just test it out :

select a.rec_key, a.table1_data1, a.table1_data2, c.who_rec_key,
first_table a,
select *
from (
select  row_number() over ( partition by rec_key order by date1 desc) rr, s.rec_key, s.who_rec_key
from second_table s )
where rr = 1 ) b,
third_table c
where substr(a.rec_key,1,15) = b.rec_key
and b.who_rec_key = c.who_rec_key  

Expert Comment

ID: 35015810
Try the below query:
select * from (
select rec_key, table1_data1, table1_data2, who_rec_key, name, 
rank() over (partition by substr(main_table.rec_key,1,15) order by to_date(||' '||second_table.hour, 'YYYYMMDD HH:MI AM') Rnk
from main_table, second_table, third_table
where substr(main_table.rec_key,1,15) = second_table.rec_key and
      second_table.who_rec_key = third_table.who_rec_key
where rnk = 1

Open in new window


Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now