X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D47023A3B2A for ; Fri, 5 Nov 2004 14:51:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33807-09 for ; Fri, 5 Nov 2004 14:51:45 +0000 (GMT) Received: from mail2.itxmarket.com (fw.itxmarket.com [213.11.40.109]) by svr1.postgresql.org (Postfix) with ESMTP id 519AF3A2011 for ; Fri, 5 Nov 2004 14:51:48 +0000 (GMT) Received: from icommerce.fr (mich2.itxmarket.com [192.168.2.26]) by mail2.itxmarket.com (Postfix) with ESMTP id 427A71C091F; Fri, 5 Nov 2004 15:51:47 +0100 (CET) Received: by icommerce.fr (Postfix, from userid 1001) id 16B412284E; Fri, 5 Nov 2004 15:51:47 +0100 (CET) Date: Fri, 5 Nov 2004 15:51:46 +0100 From: "Michael L. Hostbaek" To: Franco Bruno Borghesi Cc: pgsql-sql@postgresql.org Subject: Re: Group by and aggregates Message-ID: <20041105145146.GB47770@mich2.itxmarket.com> Mail-Followup-To: "Michael L. Hostbaek" , Franco Bruno Borghesi , pgsql-sql@postgresql.org References: <20041104165430.GD94770@mich2.itxmarket.com> <1099590115.1475.5.camel@taz.oficina> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="Kj7319i9nmIyA2yE" Content-Disposition: inline In-Reply-To: <1099590115.1475.5.camel@taz.oficina> X-Operating-System: FreeBSD 5.2.1-RELEASE-p8 i386 X-PGP-Key: http://www.freebsdcluster.org/~mich/ User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/44 X-Sequence-Number: 19363 --Kj7319i9nmIyA2yE Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Franco Bruno Borghesi (franco) writes: > If I understand well, you want the highest cmup for each partno, that is > max(cmup) grouped by partno (only).=20 >=20 > SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS > max_cmup, sum(T.qty) AS sum_qty > FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM > my_table GROUP BY partno) AS TMP > WHERE tmp.partno=3DT.partno > GROUP BY T.partno, TMP.max_cmup_for_partno, T.status >=20 > Hope it helped. This worked out nicely. Thank you very much ! /mich --Kj7319i9nmIyA2yE Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (FreeBSD) iD8DBQFBi5OCewpSEg9V9r4RAh9nAJ45FD6GWngd1EOvYQiUxlxULFmGqACfcqj+ BhQmt0IL5x1jH+iG3uMImsM= =u+0w -----END PGP SIGNATURE----- --Kj7319i9nmIyA2yE--