include ids in query grouped by multipe values

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: karsten <karsten@terragis.net>
To: pgsql-sql@postgresql.org
Subject: include ids in query grouped by multipe values
Date: Mon, 17 Feb 2014 11:49:38 -0800
Message-ID: <C2D599BB8E04404B8FB0169FA17B5248@terragis2> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (3+ messages)  latest in thread

Message-ID: <C2D599BB8E04404B8FB0169FA17B5248@terragis2>
Permalink:  ../C2D599BB8E04404B8FB0169FA17B5248@terragis2/
Also on:    postgresql.org/message-id/C2D599BB8E04404B8FB0169FA17B5248@terragis2

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: karsten@terragis.net
  Subject: Re: include ids in query grouped by multipe values
  In-Reply-To: <C2D599BB8E04404B8FB0169FA17B5248@terragis2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox