CODEWITHSUNDEEP
sqloracle-databaseplsql
Futochan
Futochan

Reputation: 331

PLSQL & Oracle - Join 2 tables and only return based on latest date

this is a question for plsql & oracles. I am new to this. Please help!

I have 2 tables: Table A and Table B

Table A: ID, Date
Table B: Name, Address

How do I do a join between 2 tables and then have it just return based on latest date. Also, it will be based on specified ID in a list.

My current query returns this:

1 | 1/1/2013 | Apple | 123 Malcolm
1 | 1/2/2013 | Apple | 123 Malcolm
1 | 1/3/2013 | Apple | 123 Malcolm
3 | 1/1/2013 | Orange| 124 Malcolm
3 | 1/2/2013 | Orange| 124 Malcolm

How do I get it to return just:

1 | 1/3/2013 | Apple | 123 Malcolm
3 | 1/2/2013 | Orange| 124 Malcolm

select unique(ID), a.Date, b.Name, b.Address
from tableA a
join tableB b
on a.ID = b.ID
where a.Date > TO_DATE('12/31/2012', 'mm/dd/yyyy') and a.ID in ('1', '3')

Thanks!

Upvotes: 0

Views: 1129

Answers (5)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Using Analytical functions like RANK() will be the best option.

Upvotes: 0

TommCatt
TommCatt

Reputation: 5636

Here's a fast and simple solution. I assume that ID and Date in TableA form the PK, so are indexed. The subquery finds the Max date for the specified ID and does it quickly due to the index. That record is then used to join to TableB. I use this format all the time when looking through a sequence of dates to find the latest (Max), or earliest (Min). You'll find it acceptably fast even if TableA is very large.

with
TableA as (
  select 1 id, date '2013-01-01' selected from dual union all
  select 1, date '2013-01-02'from dual union all
  select 1, date '2013-01-03'from dual union all
  select 3, date '2013-01-01' from dual union all
  select 3, date '2013-01-02' from dual
),
TableB as (
  select 1 id, 'Apple' name, '123 Malcolm' addr from dual union all
  select 3, 'Orange', '124 Malcolm' from dual
)
-- Here is the query:
select *
from   TableB b
join   TableA a
  on   a.id = b.id
   and a.selected =(
         select Max( selected )
         from   TableA
         where  id = a.id
     );

Upvotes: 0

zundarz
zundarz

Reputation: 1594

This way uses analytics:

select a_id,
       a_date,
       b_name,
       b_address
from (
select a.id   as a_id,
       a.date as a_date,
       b.name as b_name,
       b.address as b_address,
       rank() over (partition by a.name_id
                        order by a.date desc) as rnk
from a inner join b on a.id=b.id
 where a.id in ('1','3') and 
       a.date > to_date('12/31/2013','mm/dd/yyyy')
)
where rnk=1

Upvotes: 1

Steffen Winkler
Steffen Winkler

Reputation: 2864

You need to group your result set. Also you need an aggregat function, in this case MAX()

This should work:

select unique(ID), MAX(a.Date), b.Name, b.Address
from tableA a
join tableB b
on a.ID = b.ID
where a.Date > TO_DATE('12/31/2012', 'mm/dd/yyyy') and a.ID in ('1', '3')
group by ID, b.Name, b.Address

You can read up on those methods and more over at http://www.w3schools.com/sql

Link to the GROUP BY explanation: http://www.w3schools.com/sql/sql_groupby.asp

Link to the MAX() explanation: http://www.w3schools.com/sql/sql_func_max.asp

Upvotes: 3

xQbert
xQbert

Reputation: 35323

There's lots of ways to do this:

This is one. It gets a subset of max dates by the ID and then joins it into what you have already thereby limiting to the max of each group.

Select A.ID, A.Date, B.Name, B.Address
FROM A
INNER JOIN B
 on A.ID = B.ID
INNER JOIN (Select max(date) maxDate, ID from A group by ID) C
 on C.ID=A.ID and C.MaxDate = A.Date
WHERE
A.ID IN ('1','3')

Upvotes: 2

Related Questions