This is one of those things that's easier to handle procedurally. Add an
ORDER BY clause for fault_no (ascending) and response_no (descending), then
use just the first row you fetch for each fault_no.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Stacy Mader" <[EMAIL PROTECTED]>
To: "dbi-users" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2001 7:27 PM
Subject: Multiple table queries
> I am trying to perform a query from two tables linked by the field
> fault_no. My SQL is:
>
> SELECT report.fault_no,
> response.response_no,
> response.category
> FROM report,
> response
> WHERE report.fault_no = response.fault_no(+)
>
> Now the problem with this statement is that some of the results are
> repeated. For example:
>
> '1189','460','Other'
> '1189','457','Telescope control system'
> '1189','653','Drive system - az'
> '1190','451','Telescope control system'
> '1190','465','Telescope control system'
>
> The reason for this is that although fault_no is distinct, a fault_no
> can have more than one
> response_no!
>
> So my question is: for fault_no's with multiple response_no's, can
> I modify the SQL to just
> return results with the latest response_no only? For example, from the
> above query, I'd
> like to have returned:
>
> '1189','653','Drive system - az'
> '1190','465','Telescope control system'
