Hi Primoz!

As long as there aren't any duplicate records in Table 2, you can try
this:

select Table1.*, Table2.Field2
from Table1
join Table2 t2a on Table1.Field1 = Table2.Field1)
where not exists(select * from Table2 t2b
where t2b.Field1 = t2a.Field1
and t2b.Field2 < t2a.Field2)
and (...search condition...)

Report back to this list whether this is faster, identical or slower
than your solution (I use EXISTS myself with fb 1.0, so I know it is
fast enough for me, but I've never tried using SELECT FIRST in a
subselect so I don't know how long time your query needs for
executing).

Set

--- In firebird-support@yahoogroups.com, Primoz wrote:
> Hello!
>
> In first table I have master records. In second table there are
> details (one or more records for each record from first table).
>
> Table 1:
> Field1, Field2
> A1, C1
> A2, C2
>
> Table 2:
> Field1, Field2
> A1, D1
> A1, D2
> A2, E1
> A2, E2
>
> Joining this two tables on Field1 would give me this result:
> A1, C1, D1
> A1, C1, D2
> A2, C2, E1
> A2, C2, E2
>
> What I realy want is result, where I get joined result, but just on
> first record from second table.
> Like this:
> A1, C1, D1
> A2, C2, E1
>
>
> Searching for solution I found folowing:
>
> select Table1.*, (select first 1 Table2.Field2 from Table2 where
> Table1.Field1 = Table2.Field1)
> from Table1
> where (...search condition...)
>
> It works, but I have a feeling, that this is not fastest solution.
> Is there any other approach for this situation ?