Subject Re: select question - faster method
Author Svein Erling Tysvær
Hi Primoz!

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

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 ( 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


--- In, 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 ( condition...)
> It works, but I have a feeling, that this is not fastest solution.
> Is there any other approach for this situation ?