From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: tricky GROUP BY / JOIN question
Date: Sun, 07 Nov 2004 10:31:08 +0000
Message-ID: <418DF96C.4090501@numerixtechnology.de> (raw)
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
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: mailreg@numerixtechnology.de
Subject: Re: tricky GROUP BY / JOIN question
In-Reply-To: <418DF96C.4090501@numerixtechnology.de>
* 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