Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Portable SQL to fetch only one, latest record from join result?

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

From: <jarl_at_mimer.com>
Date: 19 Apr 2007 05:40:15 -0700
Message-ID: <1176986415.093976.266420@y5g2000hsa.googlegroups.com>


On Apr 17, 8:49 pm, MikeJohnson <mikejohn..._at_volcanomail.com> wrote:
> 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

SELECT name, date, description
FROM customer AS c, orders AS o
WHERE c.id = o.CustId
AND date = (select max(date) from orders where CustId = c.id);

Core SQL-99, i.e. probably very portable.

Note that NAME and DATE are reserved words in ANSI SQL, so if you are going to keep them you'd better double quote them, i.e. "NAME", "DATE". (For a full list of ANSI/ISO SQL reserved words, see http://developer.mimer.com/validator/sql-reserved-words.tml)

HTH,
Jarl Received on Thu Apr 19 2007 - 07:40:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US