Thanks for the question, Kevin.

Asked: May 23, 2012 - 7:03 pm UTC

Last updated: September 14, 2017 - 3:13 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 50K+ times! This question is

You Asked

I have two tables

create table g ( a int, d date);
 
with this data in it:
 
insert into g values ( 1, to_date('01/15/2004','mm/dd/yyyy'));
insert into g values ( 2, to_date('01/15/2004','mm/dd/yyyy'));
insert into g values ( 3, to_date('01/15/2004','mm/dd/yyyy'));

create table n ( a int, s int, name varchar2(30 byte));

with this data in it:

insert into n values (1, 0, 'Fred');
insert into n values (2, 0, 'John');
insert into n values (2, 1, 'Mary');

Looking for these results:

a      name
------------------------
1      Fred
2      John
3      Not Found

Tried this:

    SELECT
          a,
          NVL(n.name,'Not Found')
     FROM g LEFT OUTER JOIN n
    USING (a)
    WHERE d = to_date('01/15/2004','mm/dd/yyyy')
      AND n.s = (
        SELECT MIN(ns.s)
          FROM n ns
         WHERE ns.a  = a
      );

Results:

a      name
------------------------
1      Fred
2      John

If the sub-query is removed:

a      name
------------------------
1      Fred
2      John
2      Mary
3      Not Found

and Tom said...

ops$tkyte%ORA11GR2> select g.a, (select max(name) keep (dense_rank first order by s)
  2                 from n
  3                where n.a = g.a) name
  4    from g
  5  /

         A NAME
---------- ------------------------------
         1 Fred
         2 John
         3

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select g.a, x.name
  2    from g left outer join
  3         (select *
  4            from (select n.*, row_number() over (partition by a order by s) rn
  5                    from n)
  6           where rn = 1 ) X on ( g.a = x.a )
  7  /

         A NAME
---------- ------------------------------
         1 Fred
         2 John
         3

the first would be OK if you are getting a few rows from G and have an index on N(A)

the second would be good if you are getting most every row from G.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Join of two tables and want first row of matching records in second table

Kevin Marshall, May 24, 2012 - 1:46 pm UTC

Thank you. Both options are great. WOW!

The second option may be the better fit as the final solution will involve joins on multiple tables with multiple columns taken from each. Based on this, most of the final solution is working.

A reader, August 11, 2015 - 8:45 pm UTC

in this similar question, how can I avoid writing the two subqueries.

create table g(a int, b varchar2(2), d date) -- a, b and d are together unique;
insert into g values (1, 'ER', to_date('01012005','mmddyyyy');
insert into g values (1, 'ER', to_date('06012005','mmddyyyy');
insert into g values (1, 'ER', to_date('01012015','mmddyyyy');
insert into g values (2, 'PR', to_date('01012005','mmddyyyy');
insert into g values (2, 'PR', to_date('01012015','mmddyyyy');
insert into g values (2, 'PR', to_date('06012015','mmddyyyy');
insert into g values (3, 'FR', to_date('06012015','mmddyyyy');

create table n(a int, b varchar2(2), d date, e varchar2(3), f varchar2(3)) -- a, b and d are unique;
insert into n values(1, 'ER', to_date('01012005','mmddyyyy', 'FSC', 'COL');
insert into n values(1, 'ER', to_date('01012014','mmddyyyy', 'FOO', 'CLL');
insert into n values(1, 'PR', to_date('01012014','mmddyyyy', 'FOI', 'COL');
insert into n values(1, 'PR', to_date('06012014','mmddyyyy', 'FIT', 'CLL');

select g.a, g.b, g.d,
(select n.e from n where n.a = g.a and n.b = g.b and n.d = (select max(n.d) from n where n.a = g.a and n.b = g.b and n.d<=g.d) e,
(select n.f from n where n.a = g.a and n.b = g.b and n.d = (select max(n.d) from n where n.a = g.a and n.b = g.b and n.d<=g.d) f
from g

Awesome

A reader, September 13, 2017 - 5:55 pm UTC

Found this post for my problem and glad I did. I had a view that was joining two tables where there is 1 row in table A and zero to many in table B. Issue was that if table B had 4 rows my view would return 4 duplicate rows and distinct could not be used since to_clob for varchar CASE statements were in use. This example applied to my view was the answer, THANKS TOM!

September 14, 2017 - 3:13 am UTC

That's why we never delete stuff in AskTom :-)

Very Useful

A reader, September 11, 2018 - 2:56 pm UTC

Very Useful solution, worked for me in a similar case.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer