Problem with a query with MAX function

[Nicole]

I have this query:
SELECT AGG_NAME, MAX(AGG_RELNUM) RELNUM, MAX(AGG_RELSPEC ) RELSPEC, MAX(ED)
EDIZ

FROM
(SELECT .....
) AT GROUP BY AGG_NAME

The problem that the query reply records in which the values is different
from the value in tables. My query is correct???
Is possible to use three MAX in the same query??

Thank you

[Serge Rielau]

Re: Problem with a query with MAX function

Nicole wrote:[color=blue]
> I have this query:
> SELECT AGG_NAME, MAX(AGG_RELNUM) RELNUM, MAX(AGG_RELSPEC ) RELSPEC, MAX(ED)
> EDIZ
>
> FROM
> (SELECT .....
> ) AT GROUP BY AGG_NAME
>
> The problem that the query reply records in which the values is different
> from the value in tables. My query is correct???[/color]
You curent query has 3 independent MAX() functions. That is, while
every values RELNUM, RELSPEC and EDIZ was coming ultimately in query
below the row (AGGNAME, RELNUM, RELSPEC, EDIZ) may not exist.
To answer the request:
"Give me the rows AGG_NAME for which AGG_RELNUM, .. is maximum!"
you need OLAP:
SELECT AGGNAME, AGG_RELNUM, AGG_RELSPEC, ED
FROM
(SELECT ROW_NUMBER()
OVER(PARTITION BY AGGNAME
ORDER BY AGG_RELNUM DESC, AGG_RELSPEC DESC, ED
DESC) AS rn,
AGG_NAME, AGG_RELNUM, AGG_RELSPEC, ED
FROM (....) AS T) AS S
WHERE rn = 1;

(you can add/remove elements to the PARTITIONIN BY and the ORDER BY
clause as required for your semantics.
[color=blue]
> Is possible to use three MAX in the same query??[/color]
Yes, absolutely[color=blue]
>
> Thank you
>
>[/color]


--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab