Aggregate function -MAX, What this function exactly does

siva-ongole · New User Joined: 09 Jan 2007 Posts: 2 Location: Hyderabad

Hi ,

Please help me in understanding the MAX function ??
What this function exactly does ??

Since I have big three table join query and not able to understand it clearly.

The query looks like

SELECT
MAX(COLUMNA) ,
MAX(COLUMNB) ,
MAX(COLUMNC) ,
MAX(COLUMND) ,
MAX(COLUMNE) ,
MAX(COLUMNF) ,
MAX(COLUMNG) ,
MAX(COLUMNH) ,
COLUMNI ,
COLUMNJ ,
SUM(COLUMNK) ,
SUM(COLUMNL) ,
SUM(COLUMNM) ,
-- *
FROM
TABLE1
GROUP BY
COLUMNA
COLUMNB
COLUMNC
COLUMND
COLUMNE

aS OF I KNOW the MAX function should fetch the maximum value so should fetch one row.But where this query is fetching multiple rows.

Pleazz help me to understand this.

guptae · Posted: Mon Feb 19, 2007 4:28 pm

Hi There,

Ya it will fetch multiple rows u will get one row for every record present in table where first eight field will be replaced by max value for that field in table , then u will get COLUMNI & COLUMNj for that record after that last three field will conatin the sum value for respected field

siva-ongole · New User Joined: 09 Jan 2007 Posts: 2 Location: Hyderabad

Hi Moderator,

That means if i have 100 rows in table and only one row is having the maximum values then you mean all 100 rows will be displayed with actual columnI and COLUMNJ value but other values will be maximum values ???

if this is the case that is not happening.Will send u sample data returned.

003 18095 70 70 000 S 029 003 010 P
003 18095 70 70 000 S 029 003 010 Y
003 18264 70 70 000 S 014 003 010 P
003 18264 70 70 000 S 014 003 010 Y

I know that 18264 is the maximum value for the columnb.Then why is the 18095 is also getting returned.

thanks

guptae · Posted: Mon Feb 19, 2007 4:49 pm

Hi there ,

Apologies for my prev post i have not looked on group by clause