Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFUCI-00014s-4X for pgsql-sql@arkaria.postgresql.org; Mon, 17 Feb 2014 19:49:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WFUCH-0002o7-Fw for pgsql-sql@arkaria.postgresql.org; Mon, 17 Feb 2014 19:49:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFUCG-0002ny-30 for pgsql-sql@postgresql.org; Mon, 17 Feb 2014 19:49:44 +0000 Received: from caibbdcaabbg.dreamhost.com ([208.113.200.116] helo=homiemail-a92.g.dreamhost.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFUCC-0003hD-DR for pgsql-sql@postgresql.org; Mon, 17 Feb 2014 19:49:43 +0000 Received: from homiemail-a92.g.dreamhost.com (localhost [127.0.0.1]) by homiemail-a92.g.dreamhost.com (Postfix) with ESMTP id 8C1D43DC06D for ; Mon, 17 Feb 2014 11:49:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=terragis.net; h=reply-to :from:to:subject:date:message-id:mime-version:content-type; s= terragis.net; bh=YNtSvNnPKDceuxj/R45W4eG/xHQ=; b=tKWTi+a5Dy9MAm4 PZPfPUDpk1siy2qPXsT2QBIeb1Vmaqh0eCeVUo03XqR5GAp/8nS+7GTHo2PFaR7j MKDshLMiv0zbtFHNJ/x2yVGIvUhTTyHDDiajZtwqaEE2VWeKmKjax17p9tNi3hIC 0zGAy0q6wqSjTA+5TfffygWSP3KM= Received: from terragis2 (c-98-247-240-32.hsd1.wa.comcast.net [98.247.240.32]) (Authenticated sender: karsten@terragis.net) by homiemail-a92.g.dreamhost.com (Postfix) with ESMTPA id 5BCD23DC05E for ; Mon, 17 Feb 2014 11:49:38 -0800 (PST) Reply-To: From: "karsten" To: Subject: include ids in query grouped by multipe values Date: Mon, 17 Feb 2014 11:49:38 -0800 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0062_01CF2BD6.563ABF00" X-Mailer: Microsoft Office Outlook 11 Thread-Index: Ac8sGWQZtMYx5r0PRnGVKGtyFUE8XQ== X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609 X-Pg-Spam-Score: 0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. ------=_NextPart_000_0062_01CF2BD6.563ABF00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi Group, for several days I have been trying to resolve the following task below, but all my attempts so far appear to give me back too many rows: I have data kind like this in TableA: id IDone IDtwo sale 1 010 200 8000 2 010 200 7851 3 010 200 517 4 020 210 5730 5 020 210 2000 6 020 230 3170 7 020 230 3170 8 020 230 2051 9 030 230 0 With the query below I can basically select the maximum sale for each IDone - IDtwo combination - so almost what I need: select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone, IDtwo; But it would want to also select the id column ( and all other additonal 20 columns of TabelA not shown above) and need that there is only one record returned for each IDone - IDtwo combination. I tried SELECT a.id, a.IDone, a.IDtwo, a.sale FROM TableA a inner join ( select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone, IDtwo ) b on a.IDone = b.IDone and b.IDtwo = b.IDtwo and a.sale = b.maxsale; but that returns too many rows and I do not understand why How can I resolve this ? Karsten Vennemann Terra GIS LTD ------=_NextPart_000_0062_01CF2BD6.563ABF00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi=20 Group,
 
for=20 several days I have been trying to resolve the following task below, but = all my=20 attempts so far appear to give me back too many = rows:
 
I have data kind like this in TableA:
 
id  =20 IDone  IDtwo  sale
1    010   =20 200    8000
2    010   =20 200    7851
3    010   =20 200    517
4    020   =20 210    5730
5    020   =20 210    2000
6    020   =20 230    3170
7    020   =20 230    3170
8    020   =20 230    2051
9    030   =20 230    0
 
With the=20 query below I can basically select the maximum sale for each IDone = - IDtwo=20 combination - so almost what I need:
 
select=20 IDone, IDtwo, max(sale) as maxsale FROM TableA=20 group by IDone, IDtwo;
 
But it=20 would want to also select the id column ( and all other additonal 20 = columns of=20 TabelA not shown above) and need that there is only one record returned = for each=20 IDone - IDtwo combination. I tried=20
 
SELECT=20 a.id, a.IDone, a.IDtwo, a.sale FROM TableA=20 a
inner=20 join ( select=20 IDone, IDtwo, max(sale) as maxsale FROM TableA=20 group by IDone, IDtwo ) = b
on=20 a.IDone =3D b.IDone and b.IDtwo =3D b.IDtwo  
and = a.sale =3D=20 b.maxsale;
 
but that=20 returns too many rows and I do not understand = why
How can I=20 resolve this ?
 
Karsten = Vennemann
Terra GIS=20 LTD
------=_NextPart_000_0062_01CF2BD6.563ABF00--