Home » SQL & PL/SQL » SQL & PL/SQL » Top 1 from Subtable
Top 1 from Subtable [message #238105] Wed, 16 May 2007 13:35 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
Hey everyone.

I have a sub table that is stores comments for accounts.

I basicly need to pull a query that where the update date on the account is later then then comment date of accounts that have a comment of "XYZ". However, there can be more then 1 "XYZ".

I basicly need to do a join but, need to compare the date to the latest comment date.

Any ideas?
Re: Top 1 from Subtable [message #238108 is a reply to message #238105] Wed, 16 May 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a sub table? Do you mean a nested table?

Post a test case (create table and insert statements) and explain on this example what you have and what you want.

Don't forget to tell your Oracle version (4 decimals) and to format your post.

Regards
Michel
Re: Top 1 from Subtable [message #238119 is a reply to message #238105] Wed, 16 May 2007 14:28 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Using Oracle 9i Release 9.2.0.4.0

This is a 3rd party system and have access to only query the tables.

The tables could look like

tblAccount
-AccountNum
-AccountDateUpdate

tblRemarks
-AccountNum
-Remark
-RemarkDate

I need to pull accounts that have a remark like 'xyz%'
and the AccountDateUpdate > RemarkDate.

But the problem is i need it to be the last remark date if there are more then one 'xyz' comment on the account.

I dont have the ability to create tables/insert commands because i do not have a Oracle DB to work on.

Re: Top 1 from Subtable [message #238124 is a reply to message #238119] Wed, 16 May 2007 14:45 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  data as (
    select a.accountnum, a.accountdateupdate, r.remark, r.remarkdate,
           row_number() over(partition by a.accountnum order by r.remarkdate desc) rn
    from tblaccount a, tblremarks r
    where r.accountnum = a.accountnum
      and r.RemarkDate < a.AccountDateUpdate 
      and r.remark like 'xyz%'
  )
select a.accountnum, a.accountdateupdate, r.remark, r.remarkdate
from data
where rn = 1
/

Regards
Michel
Previous Topic: interpreting fields..
Next Topic: Multiple rows been pulled from select...Incorrect Join??
Goto Forum:
  


Current Time: Sat Dec 10 14:59:43 CST 2016

Total time taken to generate the page: 0.08195 seconds