Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / May 2005

Tip: Looking for answers? Try searching our database.

Problem with a query with MAX function

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Nicole - 31 May 2005 15:25 GMT
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 - 31 May 2005 15:55 GMT
> I have this query:
> SELECT AGG_NAME, MAX(AGG_RELNUM) RELNUM, MAX(AGG_RELSPEC) RELSPEC, MAX(ED)
[quoted text clipped - 6 lines]
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.