>as the where clause doesn't compare to a constant value but a field in the table to be joined I don't believe it can be optimized by rushmore: the optimizer would have to "understand" the "distinctness" of cl_ref in the subselect. ***If*** that was not the case, how should a record in p be flagged which has cl_dob> AdjRefDate of Recno()=1 but cl_dob< AdjRefDate of Recno()=15 ? But since cl_ref is unique, perhaps a compound index could be used to bisect p for each record in the sub-select. Maybe Aleksey will chime in, but I think this could be one of the situations where hand-optimization can make a difference. One of the things I'ld try would be a double scan loop with r in the outer loop jumping over all the records in person with the same cl_Ref but not fitting the date criteria. The effectiveness should grow the smaller reccount() is in the grouped sub-select, giving each r.cl_Ref the possibility of dicarding many records on p.cl_dob without actually checking them, as I guess the
>SQL has to do.

Hi Thomas,

My intention was to avoid double JOIN for the person and referral tables reported by SYS(3054,11) for previous query and see if it improver performance.