Return-Path: 
 <derby-user-return-6104-apmail-db-derby-user-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: (qmail 30686 invoked from network); 5 Feb 2007 23:51:27 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 5 Feb 2007 23:51:27 -0000
Received: (qmail 83127 invoked by uid 500); 5 Feb 2007 23:51:33 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 83097 invoked by uid 500); 5 Feb 2007 23:51:33 -0000
Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-user-help@db.apache.org>
list-unsubscribe: <mailto:derby-user-unsubscribe@db.apache.org>
List-Post: <mailto:derby-user@db.apache.org>
List-Id: <derby-user.db.apache.org>
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Delivered-To: mailing list derby-user@db.apache.org
Received: (qmail 83085 invoked by uid 99); 5 Feb 2007 23:51:33 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Feb 2007 15:51:33 -0800
X-ASF-Spam-Status: No, hits=-0.0 required=10.0
	tests=SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: domain of naarani@gmail.com designates
 209.85.132.247 as permitted sender)
Received: from [209.85.132.247] (HELO an-out-0708.google.com) (209.85.132.247)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Feb 2007 15:51:22 -0800
Received: by an-out-0708.google.com with SMTP id c25so1055534ana
        for <derby-user@db.apache.org>; Mon, 05 Feb 2007 15:51:01 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=tK+rHBRK8vrTFYMgjFYPlJdoAnIELDy1Xd1toLm9rm2urWbEb/m/Y1dyrje1pgh/UhfCxQ/pR/CamQu5G2UsNj4S0wmhRP0vy1QTj4mqFRWvGtb8Neu9Xsy5EgdpBg04g5/m4PRmnR0nwqFMqEEKRt7Ax86VPIVEBF4DYK3C4U8=
Received: by 10.114.111.1 with SMTP id j1mr694290wac.1170719460703;
        Mon, 05 Feb 2007 15:51:00 -0800 (PST)
Received: by 10.114.241.12 with HTTP; Mon, 5 Feb 2007 15:51:00 -0800 (PST)
Message-ID: <3b2806290702051551q236359cat780d17686a2e68db@mail.gmail.com>
Date: Tue, 6 Feb 2007 00:51:00 +0100
From: "Diego Zanga" <naarani@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Group By - problems
In-Reply-To: <5F8CB685-779C-4B59-8462-6853A3DF7FD3@SUN.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3b2806290702051331u1fd4b289ya79b1f9bc181705e@mail.gmail.com>
	 <5F8CB685-779C-4B59-8462-6853A3DF7FD3@SUN.com>
X-Virus-Checked: Checked by ClamAV on apache.org

2007/2/5, Craig L Russell <Craig.Russell@sun.com>:
> Hi Diego,
>
> The message is correct and Derby is behaving as it should.
>
> If you are using GROUP BY, you are aggregating a number of rows into
> a smaller number of rows, so instead of e.g. 100 rows all of which
[..]
> So you could e.g. aggregate clienti.birthday to find the youngest and
> oldest clienti in each provincia using SELECT provincia, MIN
> (clienti.birthday), MAX(clienti.birthday) FROM clienti GROUP BY
> clienti.provincia but this doesn't sound like what you are trying to do.
>
>  From the looks of the query, you don't want the GROUP BY clause at
> all...

it was made to fix a problem with the query made by an <engine>,
however i've fixed the engine

the problem is the query was something like
select a.ID, a.name, b.name from a,b where a.idb=b.id group by a.id

<a.id isn't in b table>, so the group by fail (works with mysql but
not with derby)

this one works
select a.ID, a.name, b.name from a left join b on a.idb=b.id where
a.idb=b.id group by a.id

and the group by is no more needed
select a.ID, a.name, b.name from a left join b on a.idb=b.id where a.idb=b.id


cya, thx :-)
-- 
Diego Zanga
------------------------------
http://www.eLawOffice.it
http://www.blogstudiolegale.eu
http://www.javablog.eu
Skype NAARANI