From: Tom Lane <tgl@sss.pgh.pa.us>
To: mailreg@numerixtechnology.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: tricky GROUP BY / JOIN question
Date: Sun, 07 Nov 2004 13:31:56 -0500
Message-ID: <22995.1099852316@sss.pgh.pa.us> (raw)
In-Reply-To: <418DF96C.4090501@numerixtechnology.de>
References: <418DF96C.4090501@numerixtechnology.de>
T E Schmitz <mailreg@numerixtechnology.de> writes:
> This is *almost* what I need:
> SELECT
> BRAND.BRAND_NAME,
> MODEL.MODEL_NAME,
> min (ITEM.PRICE),max (ITEM.PRICE)
> *min (CONDITION.POSITION),max (CONDITION.POSITION)*
> FROM ITEM
> left outer join MODEL on MODEL_PK =ITEM.MODEL_FK
> left outer join BRAND on BRAND.BRAND_PK =MODEL.BRAND_FK
> left outer join CONDITION on CONDITION.CONDITION_PK = ITEM.CONDITION_FK
> group by BRAND.BRAND_NAME,MODEL.MODEL_NAME
> In the result I don't want min/max(POSITION) but CONDITION.NAME of min
> and max(POSITION) for each MODEL.
I think you could do something like
SELECT
BRAND.BRAND_NAME,
MODEL.MODEL_NAME,
min (ITEM.PRICE),max (ITEM.PRICE)
(select name from condition c1 where position = min(condition.position)),
(select name from condition c2 where position = max(condition.position)),
FROM ITEM
left outer join MODEL on MODEL_PK =ITEM.MODEL_FK
left outer join BRAND on BRAND.BRAND_PK =MODEL.BRAND_FK
left outer join CONDITION on CONDITION.CONDITION_PK = ITEM.CONDITION_FK
group by BRAND.BRAND_NAME,MODEL.MODEL_NAME
ie do a sub-select to get the desired name.
You need Postgres 7.4 or later to get this to work --- before that we
would have mis-interpreted the aggregate calls to indicate aggregation
within the sub-selects. The current interpretation is per SQL spec:
since the aggregate argument is a variable of the outer select, the
aggregation occurs with respect to that select, and the aggregate result
is passed down to the sub-select as a scalar.
regards, tom lane
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: tgl@sss.pgh.pa.us, mailreg@numerixtechnology.de
Subject: Re: tricky GROUP BY / JOIN question
In-Reply-To: <22995.1099852316@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox