Обсуждение: tricky GROUP BY / JOIN question

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

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

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