Home » SQL & PL/SQL » SQL & PL/SQL » Need help joining tables and getting max value
Need help joining tables and getting max value [message #188995] Tue, 22 August 2006 12:54 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
Need help trying to join two tables together. I want to join table 1 with table 2 but only where Table2.date is greater than Table1.date.

If i do

select * from table1, table2
where T1.myID = T2.myId and T2.date > T1.date;

I'll end up with extra rows. How can i get rid of the extra rows so that i'll only get the maxium dates from table 2?
Tried using max(T2.date) > T1.date but it won't work.



Table1
myID Date
1 Jan 2002
2 Jan 2003
3 Jan 2001


Table2
myID Date
1 Jan 2002
1 Jan 2003
1 Jan 2004
2 Jan 2004
2 Jan 2005
3 Jan 2004


Wanted result
myID TBL1.Date TBL2.Date
1 Jan 2002 Jan 2004
2 Jan 2003 Jan 2005
3 Jan 2001 Jan 2004

Report message to a moderator

Re: Need help joining tables and getting max value [message #189008 is a reply to message #188995] Tue, 22 August 2006 14:41 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
See if this query can help you

SELECT TAB1.MYID ,DATE1,DATE2 FROM TABLE1,(SELECT MYID,MAX(DATE2)DATE2 FROM TABLE2 GROUP BY MYID)TAB1
WHERE TABLE1.MYID=TAB1.MYID;

Report message to a moderator

Re: Need help joining tables and getting max value [message #189064 is a reply to message #188995] Wed, 23 August 2006 01:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't use restricted words (such as DATE, NUMBER etc) as column names - it causes no end of problems.

An analytic solution would work well here.

SELECT distinct t1.myid
      ,t1.date_col
      ,max(t2.date_col) over (partition by t1.myid)
FROM   table_1 t1
      ,table_2 t2
WHERE  t1.myid = t2.myid;

Report message to a moderator

Re: Need help joining tables and getting max value [message #189066 is a reply to message #189064] Wed, 23 August 2006 01:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or if you fancy a more aggregate solution:

SELECT t1.myid
      ,t1.date_col
      ,max(t2.date_col) keep (dense_rank last order by t2.date_col  )
FROM   table1 t1
      ,table2 t2
WHERE  t1.myid = t2.myid
group by t1.myid
      ,t1.date_col;

Report message to a moderator

Re: Need help joining tables and getting max value [message #189095 is a reply to message #189066] Wed, 23 August 2006 03:33 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi

Check out whether this query works.

SELECT t1.ID, t1.YEAR "DATE1", MAX(t2.YEAR )"DATE2"
FROM table1 t1, table2 t2
WHERE t1.ID = t2.ID
GROUP BY t1.ID,t1.YEAR
HAVING MAX (t2.YEAR) > t1.YEAR


Thanks & Regards,

Marlon Loyolite.

Report message to a moderator

Re: Need help joining tables and getting max value [message #189187 is a reply to message #188995] Wed, 23 August 2006 09:49 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Thanks for all the help. The queries from JRowbottom work fine but i made a mistake in what my results needed to be.

I realized that what i wanted was to list out the dates from table1 and the dates from table2 where the table2.date are greater than table1.date but are the earliest avaliable value in table2.dates

revised wanted results

myID TBL1.Date TBL2.Date
1 Jan 2002 Jan 2003
2 Jan 2003 Jan 2004
3 Jan 2001 Jan 2004

Sorry for the confusion and thank you all again very much for all the help.

Rustican

Rustican wrote on Tue, 22 August 2006 12:54

Need help trying to join two tables together. I want to join table 1 with table 2 but only where Table2.date is greater than Table1.date.

If i do

select * from table1, table2
where T1.myID = T2.myId and T2.date > T1.date;

I'll end up with extra rows. How can i get rid of the extra rows so that i'll only get the maxium dates from table 2?
Tried using max(T2.date) > T1.date but it won't work.



Table1
myID Date
1 Jan 2002
2 Jan 2003
3 Jan 2001


Table2
myID Date
1 Jan 2002
1 Jan 2003
1 Jan 2004
2 Jan 2004
2 Jan 2005
3 Jan 2004


Wanted result
myID TBL1.Date TBL2.Date
1 Jan 2002 Jan 2004
2 Jan 2003 Jan 2005
3 Jan 2001 Jan 2004

Report message to a moderator

Re: Need help joining tables and getting max value [message #189310 is a reply to message #189187] Thu, 24 August 2006 02:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should do the trick.
select t1.myid, t1.date_col,min(t2.date_col)
FROM   table1 t1
      ,table2 t2
WHERE  t1.myid = t2.myid
AND    t2.date_col > t1.date_col
group by t1.myid,t1.date_col;

Report message to a moderator

Previous Topic: Mutation Error
Next Topic: Need Help in Job Creation In ORacle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 14 01:14:44 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.