At 04:14 PM 7/07/2003 +1200, you wrote:

>The following query returns a separate line for each animal with client
>details duplicated as expected.
>
>
>select C.Number, C.Name, A.Number, A.Name
>from Client C left outer join Animal A on A.Owner = C.Number
>
>
>As a user option we need to select the first animal that matches (ie
>distinct in the client number only).
>
>Is there some easy way to do this ? A First function would have been ideal
>
>The only thing I can get working is to use an inner select statement
>using min (A.Number) - except that it takes 6 minutes instead of 6
>seconds - which is a tad too slow.

You could using First in two correlated subqueries:

select
C.Number,
C.Name
(select First 1 A.Name from Animal A
where A.Owner = C.Number) as Animal_Name,
(select First 1 B.Number from Animal B
where B.Owner = C.Number) as Animal_Number
from Client C

>Can I use a stored procedure to do this ? bearing in mind that the
>where clause cant be hardcoded in the sp.

If Client is a big table, then the two subqueries would be pretty slow. A
SP would be much quicker and you could make it so that it could return a
list of clients or a single client, e.g.

create procedure ClientsAndPets (iArgument integer)
returns (CNumber integer,
CName varchar(50),
ANumber integer,
AName varchar(25)
as
begin
if iArgument < 1 then
begin
for select c.Number, c.Name from Client c
into :CNumber, :CName do
begin
ANumber = 0;
AName = 'No Name';
select First 1 a.Number, a.Name from Animal a
where a.Owner = c.Number
into :ANumber, :AName;
Suspend;
end
end
else
begin
for select c.Number, c.Name from Client c
where c.Number = iArgument
into :CNumber, :CName do
begin
ANumber = 0;
AName = 'No Name';
select First 1 a.Number, a.Name from Animal a
where a.Owner = :iArgument
into :ANumber, :AName;
Suspend;
end
end
end

heLen