Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Portable SQL to fetch only one, latest record from join result?
Hi DBers,
This same question comes up from time to time.
I have two tables with a simple parent-child relationship:
Customer -------- Id: number Name: varchar(50) Address: varchar(100) Orders ------ CustId: number Date: date Description: varchar(255)
Assuming these values:
Customer:
1 | John | New York 2 | Mary | Calilfornia Orders: 1 | 01/01/2001 | Widget1 1 | 02/02/2002 | Widget2 2 | 03/03/2003 | Widget3 2 | 04/04/2004 | Widget4
I needed a query to just return the latest, most recent, associated child record for each master record.
In other words, I just wanted the result set:
John | 01/01/2001 | Widget1 Mary | 03/03/2003 | Widget3
I spent many hours on this, being an intermediate/beginning level SQL programmer.
I found similar examples, but unfortunately they used INNER JOIN
clauses,
which I don't think are supported by the version of Oracle I must use.
Below please find an Oracle-specific query I've come up with, which works.
I am posting it here in the hope others may find it useful.
However, is there a portable SQL way to get the same result set?
select * from (select CustId, Date, Description, row_number() over (partition by CustId order by Date desc) rn from Orders) ords, Customer cust where cust.Id = ords.CustId and ords.rn = 1
Thanks,
Mike
Received on Tue Apr 17 2007 - 13:49:23 CDT
![]() |
![]() |