Re: Portable SQL to fetch only one, latest record from join result?

On 17 Apr., 20:49, MikeJohnson <mikejohn..._at_volcanomail.com> wrote:
> I needed a query to just return the latest, most recent, associated
> child record for each master record.
>
> 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.

Inner joins are the normal joins that you always use. So it must be something else you are referring to.

> Below please find an Oracle-specific query I've come up with, which works.
> 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

A simple portable query would be to look for max date per customer in the Orders table and then select the appropriate records:

select Customer.Name, Orders.Date, Orders.Description from Orders
join Cutomer on (Customer.Id = Orders.CustId) where (Customer.CustId, Customer.Date) in   (
  select CustId, Max(Date)
  from Orders
  group by CustId
  )

This is portable, because it's Ansi SQL. Older versions of oracle however don't support that. You would have to change the join syntax:

select Customer.Name, Orders.Date, Orders.Description from Orders, Cutomer
where Customer.Id = Orders.CustId
  and (Customer.CustId, Customer.Date) in   (
  select CustId, Max(Date)
  from Orders
  group by CustId
  )

This is less readable and leads easily to incomplete joins especially when it comes to outer joins. So if you don't need to support old Oracle versions then go for the Ansi SQL syntax. Received on Thu Apr 19 2007 - 02:07:31 CDT