SQL right join, force return only one value from right hand side

Question

table 1
---
id , name

table2
---
id , activity, datefield

table1 'right join' table2 will return more than 1 results from right table (table2) . how to make it return only "1" result from table2 with the highest date

Answer 1

right join table2 on on table1.id to to select id, max = max(date) from table2

Answer 2

To retrieve the Top N records from a query, you can use the following syntax:

SELECT *
FROM (your ordered by datefield desc query with join) alias_name
WHERE rownum <= 1
ORDER BY rownum;

PS: I am not familiar with PL/SQL so maybe I'm wrong

Answer 3

You write poor information about your problem, But I'll try to make an example to help you.

You have a table "A" and a table "B" and you need to fetch the "top" date of table "B" that is related with table "A"

Example tables:

Table A:
 AID| NAME
----|-----
  1 |  Foo
  2 |  Bar

Table B:

BID | AID | DateField
----| ----| ----
 1  |   1 | 2000-01-01
 2  |   1 | 2000-01-02
 3  |   2 | 2000-01-01

If you do this sql:

SELECT * FROM A RIGHT JOIN B ON B.ID = A.ID

You get all information of A and B that is related by ID (that in this theoretical case is the field that is common for both tables to link the relation)

A.AID | A.NAME | B.BID | B.AID | B.DateField
------|--------|-------|-------|--------------
  1   |   Foo  |   1   |   1   |   2000-01-01
  1   |   Foo  |   2   |   1   |   2000-01-02
  2   |   Bar  |   3   |   2   |   2000-01-01

But you require only the last date for each element of the Table A (the top date of B)

Next if you need to get only the top DATE you need to group your query by the B.AID and fetch only the top date

SELECT 
      B.AID, First(A.NAME), MAX(B.DateField) 
FROM 
      A RIGHT JOIN B ON B.ID = A.ID 
GROUP BY 
      B.AID

And The result of this operation is:

B.AID | A.NAME | B.DateField
------|--------|--------------
  1   |   Foo  |  2000-01-02
  2   |   Bar  |  2000-01-01

In this result I removed some fields that are duplicated (like A.AID and B.AID that is the relationship between the two tables) or are not required.

• Tip: this also works if you have more tables into the sql. The sql "makes" the query and next applies a grouping for using the B to limit the repetitions of B to the top date.

Answer 4

my solution is

select from table1 right join table2 on (table1.id= table2.id and table2.datefiled= (select max(datefield) from table2 where table2.id= table1.id) )

Answer 5

Analytics!

Test data:

create table t1
  (id        number       primary key,
   name      varchar2(20) not null
  );

create table t2
  (id        number not null, 
   activity  varchar2(20) not null,
   datefield date not null
  );

insert into t1 values (1, 'foo');
insert into t1 values (2, 'bar');
insert into t1 values (3, 'baz');

insert into t2 values (1, 'foo activity 1', date '2009-01-01');
insert into t2 values (2, 'bar activity 1', date '2009-01-01');
insert into t2 values (2, 'bar activity 2', date '2010-01-01');

Query:

select id, name, activity, datefield
  from (select t1.id, t1.name, t2.id as t2_id, t2.activity, t2.datefield,
               max(datefield) over (partition by t1.id) as max_datefield
          from t1
               left join t2 
                 on t1.id = t2.id
       )
 where ( (t2_id is null) or (datefield = maxdatefield) )

The outer where clause will filter out all but the maximum date from t2 tuples, but leave in the null row where there was no matching row in t2.

Results:

        ID NAME                ACTIVITY                 DATEFIELD
---------- -------- -------------------       -------------------
         1 foo           foo activity 1       2009-01-01 00:00:00
         2 bar           bar activity 2       2010-01-01 00:00:00
         3 baz