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