I sometimes have similar requirements, and typically I then do (assuming it was second_table I wanted only the first match of):

select m.*, s.some_field as check_field, t.some_field as check_field2
from main_table m
left join second_table s
on s.field = m.field
and not exists(select * from second_table s2
where s2.field = m.field
and s2.pk+0 < s.pk) //+0 to avoid use of index
left join third_table t on t.field=s.some_field

If I were on Firebird 2.1, I would also consider using CTEs, though I don't know enough about Piotrs situation to guess how the CTE would be in his case.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Kjell Rilbe
Sent: 9. november 2009 10:19
To: firebird-support@yahoogroups.com
Subject: Re: *** SPAM (5.7) *** Re: [firebird-support] sub select befor from statment

Dimitry Sibiryakov wrote:

>>But isn't it rather complicated to achieve the "first 1" semantics in
>>the OP's way using joins?
>
> Not so complicated if you note that original subselects don't have
> ORDER BY clause.

Yes. Noted. Wonder what Piotr actually wanted to achieve...

>>How would you suggest to do that? (Not that I can't see a solution, just
>>not a very neat one...)
>
> Use aggregates. Because order is not set, MAX() will work not worse
> than MIN().

Right, works since there are no order by in the subselects. But what if
there were?? But let's wait with that until we know if that's what Piotr
wanted.

Piotr?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links