Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Aggregate function -MAX, What this function exactly does

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
siva-ongole

New User


Joined: 09 Jan 2007
Posts: 2
Location: Hyderabad

PostPosted: Mon Feb 19, 2007 4:23 pm    Post subject: Aggregate function -MAX, What this function exactly does
Reply with quote

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.
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Feb 19, 2007 4:28 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
siva-ongole

New User


Joined: 09 Jan 2007
Posts: 2
Location: Hyderabad

PostPosted: Mon Feb 19, 2007 4:45 pm    Post subject: reply
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Feb 19, 2007 4:49 pm    Post subject:
Reply with quote

Hi there ,

Apologies for my prev post i have not looked on group by clause
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am
No new posts Which function does the Ampersand (&a... Andi1982 PL/I & Assembler 3 Wed Aug 24, 2016 2:05 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us