Message-ID: <418DF96C.4090501@numerixtechnology.de>
Date: Sun, 07 Nov 2004 10:31:08 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: tricky GROUP BY / JOIN question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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