OK, I understand now what I did wrong the first time. I used extra fields
from the table in the subquery in order

to link it with the main query and these extra fields were not part of the
group by clause.



I hope I haven't wasted too much space in the group by talking about it.



It does open up a whole new area of using a subselect within a group by
query that I hadn't considered before.



Graeme









Obviously you havent tried this in sql editor.
It works perfectly.



Sorry if I have got it wrong! I didn't try this exact query but I tried a
similar one on one of my tables

in IBExpert and I got the error that I mentioned.

However I have just gone back and tried another similar one and this time it
worked!

Unfortunately I can't compare what I did originally to see what I did
different because the

query editor doesn't remember queries that failed.

Can anyone explain how it is that using a subselect to add an extra field to
a group by query

can bypass the requirement that the normal requirements for including fields
in a group by?

Graeme




Adomas

Graeme Edwards wrote:

>Syntax not checked, but idea :
> select
> orders1.cod_art,
> min(MinCost),
> ( select first 1 orders2.order_id from orders orders2
> where
> orders2.cod_art = orders1.cod_art order by mincost asc
> ) ///subselect returns first 1 order_id with cod_art =
>order1.cod_art and min(mincost)
> from
> orders orders1
> group by orders1.cod_art;
>
>
>Adomas
>
>
>
>The problem with this is that the subselect effectively creates an extra
>field in the query, and this field
>
>is not an aggregate function or contained in the group by list, so you will
>get the standard sql error to
>
>this effect.
>
>
>
>Using a stored procedure as already suggested seems to be the only way

round

>this.
>
>
>
>Graeme
>
>
>
>
>Alberto Pesce wrote:
>
>
>
>>Hi all collaborators,
>> Based on the example I need from table orders
>>
>>order_ID Cod_art Cost
>>-------- ------- ----
>>1 1 10
>>2 2 20
>>3 1 15
>>4 3 20
>>5 3 22
>>
>>this from the query
>>
>>Cod_art MinCost order_ID
>>------- -------- ---------
>>1 10 1
>>2 20 2
>>3 20 4
>>
>>Thank very much
>> Bye
>>
>>
>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>>Visit http://firebird.sourceforge.net 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
>

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336



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

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

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

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





SPONSORED LINKS


Technical
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&

> &k=Technical+support&w1=Technical+support&

w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support

Computer
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical

> &k=Computer+technical+support&w1=Technical

+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical
support

Compaq
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te

> &k=Compaq+computer+technical+support&w1=Te

chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support


Compaq
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s

> &k=Compaq+technical+support&w1=Technical+s

upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support

Hewlett
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te

> &k=Hewlett+packard+technical+support&w1=Te

chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support

Microsoft
<http://groups.yahoo.com/gads?t=ms
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica

> &k=Microsoft+technical+support&w1=Technica

l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support



_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]



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

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

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

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





SPONSORED LINKS


Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical
support

Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support


Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support

Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support

Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support



_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]