pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtricky GROUP BY / JOIN question
3+ messages / 2 participants
[nested] [flat]
* tricky GROUP BY / JOIN question
@ 2004-11-07 10:31 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: T E Schmitz @ 2004-11-07 10:31 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm sorry I couldn't think up a more precise subject line.
I would like to know whether the following can be implemented in SQL:
The example below joins 4 tables ITEM, BRAND, MODEL and CONDITION. In
human understandable terms: a [secondhand] Item is of a particular Model
and Brand. The Items retail at different prices depending on their
Condition.
Required result set:
Brand | Model | Cond | Cond | Price | Price
| | min | max | min | max
-------------------------------------------
Canon | A1 | Exc | Mint | 139 | 155
Canon | F1N | Exc++| Mint-| 329 | 379
Canon | 24mm | Exc--| Mint+| 99 | 179
Nikon | 50mm | Exc--| Mint+| 109 | 119
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
Problem:
Table CONDITION has the columns
- NAME varchar(5)
- POSITION int2
In the result I don't want min/max(POSITION) but CONDITION.NAME of min
and max(POSITION) for each MODEL.
Is this possible at all?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: tricky GROUP BY / JOIN question
@ 2004-11-07 18:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-11-07 18:31 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: tricky GROUP BY / JOIN question
@ 2004-11-09 14:46 T E Schmitz <mailreg@numerixtechnology.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: T E Schmitz @ 2004-11-09 14:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hello Tom,
Tom Lane wrote:
> 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.
>
>
> <snip>
> (select name from condition c1 where position = min(condition.position)),
> (select name from condition c2 where position = max(condition.position)),
> <snip>
Thank you, Tom, this worked a treat!
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-09 14:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-07 10:31 tricky GROUP BY / JOIN question T E Schmitz <mailreg@numerixtechnology.de>
2004-11-07 18:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-09 14:46 ` T E Schmitz <mailreg@numerixtechnology.de>
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