>>>Hi all
>>>
>>>I have a piece of SQL that really needs to be performed quicker, I have 2 tables:
>>>
>>>Person
>>>Referral
>>>
>>>Person has a date of birth and I am fetching all persons who were under 18 on their first referral, here is the SQL:
>>>
>>>

>>>select *;
>>>  from person p
>>> inner join referral r on p.cl_ref = r.cl_ref and !empty(r.re_date);
>>>      where gomonth(p.cl_dob, 216) > (select min(rr.re_date) from referral rr;
>>>                                             where rr.cl_ref = p.cl_ref and !empty(rr.re_date))
>>>

>>>
>>>According to Fox none of this is optimised, both cl_ref fields on each table are indexed.
>>>
>>>Can anyone suggest an alternative that may be quicker - or is there anything else I could do to speed up the query?
>>>
>>>Thanks
>>>Kev
>>
>>

>>SELECT P.*,r.*;
>>	FROM;
>>	FORCE (select cl_ref , min(re_date) re_date from referral rr WHERE re_date<>{} GROUP BY 1) X;
>>	JOIN person p ON p.cl_ref=x.cl_ref AND gomonth(p.cl_dob, 216) > x.re_date;
>>	JOIN referral r on r.cl_ref = p.cl_ref ;
>>	WHERE {} <> r.re_date
>>* or
>>
>>SELECT P.*,r.*;
>>	FROM;
>>	FORCE (select cl_ref , min(re_date) re_date from referral rr WHERE re_date<>{} GROUP BY 1) X;
>>	JOIN person p ON p.cl_ref=x.cl_ref AND gomonth(p.cl_dob, 216) > x.re_date;
>>	JOIN referral r on r.cl_ref = p.cl_ref ;
>>	HAVING r.re_date <> {}
>>

>
>Same performance when I try both of these....

post the performance time of the whole query, and of this part

select cl_ref , min(re_date) re_date from referral rr WHERE re_date<>{} GROUP BY 1 into cursor dummy