By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

Problem with a query with MAX function

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Nicole wrote:
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??? 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.
Is possible to use three MAX in the same query?? Yes, absolutely
Thank you

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.