What is the difference between the two records that cause the multiple lines
to be returned? Is it a date, or something else? If you are looking for
something to guarantee only the latest row is returned, you can use a
SELECT...INTO and select into a temp table grouped by the key, and using a
MAX(<datecolumn>) then join to that, (Usually more efficient, but not
always) or you can use a subquery in your SQL statement to limit to only
that one. Examples (assuming a column named ThisDate as the difference)
Select fault_no, MAX(ThisDate) as ThisDate INTO #temp
FROM report_response
GROUP BY fault_no
SELECT r.fault_no ,r.one_line_summary FROM report_response r
JOIN #temp t on r.fault_no = t.fault_no and r.ThisDate = t.ThisDate
WHERE (r.reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC
Example of the second one I put forward:
SELECT r.fault_no ,r.one_line_summary FROM report_response r
WHERE (r reported_by LIKE '%J BLOGGS%' )
and r.ThisDate = (SELECT MAX(ThisDate) WHERE fault_no = r.fault_no)
order by fault_no DESC
however, for either of them to work, you need a way to distinguish between
the row you want, and the row(s) you don't.
Enjoy,
Steve H.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 3:31 AM
To: DBI Users
Subject: SQL query
Hi all,
I have a table (report_response) which has (among others)
fault_no and response_no fields. Now a fault_no can have
multiple response_no's.
The thing is, when I issue the following SQL:
SELECT fault_no ,one_line_summary FROM report_response
WHERE (reported_by LIKE '%J BLOGGS%' ) order by fault_no DESC
my returned list displays:
1355 Glish leftovers on sagitta
1350 Site phones
1350 Site phones
See those multiple occurances of 1350? This means there are 2 responses
to the fault_no = 1350. How can I fudge the SQL
to select the last response_no submitted?
Regards,
Stacy.
BTW: Using DBI:1.14 with Oracle 7.3.3
