Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Doubt with Join & Max function

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

New User


Joined: 21 Feb 2008
Posts: 18
Location: Mumbai

PostPosted: Fri Mar 28, 2008 9:59 am    Post subject: Doubt with Join & Max function
Reply with quote

Hi,

I am new to DB2, so I was having a very basic Query. Can anybody help me on this.

Suppose, there are two tables.

TABLE: EMP

Emp No. Location

111 Mumbai
222 New York
333 Tokyo


TABLE: SALARY

Emp No. Salary

111 60000
222 80000
333 70000


I want to write a query where I can get all the details of employee with MAXIMUM SALARY.

i.e I want record

Emp No. Salary Location
222 80000 New York

Is there any approach where we can join the EMP & SALARY table and use MAX function.

Thanks in advance.
Back to top
View user's profile Send private message

abiyn

New User


Joined: 25 Mar 2008
Posts: 12
Location: Chennai

PostPosted: Fri Mar 28, 2008 10:57 am    Post subject:
Reply with quote

chiru_sh,

U can use Sub-query for this.

Select E.empid,E.location,S.salary from EMP e, Salary s where E.empid=S.empid and
S.salary=(select max(salary) from salary)


you can also Groupby function.

Regards
Abi
Back to top
View user's profile Send private message
chiru_sh

New User


Joined: 21 Feb 2008
Posts: 18
Location: Mumbai

PostPosted: Fri Mar 28, 2008 11:19 am    Post subject: Reply to: Doubt with Join & Max function
Reply with quote

Thanks for the reply.
One of the approach I could think of was:

SELECT E.EMPID,E.LOCATION,S.SALARY
FROM EMP E,SALARY S
WHERE E.EMPID=S.EMPID
ORDER BY S.SALARY DESC
FETCH 1 ROWS ONLY

Can we not get the row details where we are getting MAX VALUE like
For eg.
Approach 2 : SELECT E.EMPID,E.LOCATION,MAX(S.SALARY)
FROM EMP E,SALARY S
WHERE E.EMPID=S.EMPID

Will the above Approach 2 work. I am not sure. Please help me what will happen in above case.
Back to top
View user's profile Send private message
Richa Jain

New User


Joined: 18 Mar 2008
Posts: 35
Location: Gurgaon

PostPosted: Fri Mar 28, 2008 11:20 am    Post subject:
Reply with quote

Try this...

Code:

SELECT EMP.EMP_NO, EMP.LOCATION, SALARY.SALARY FROM EMP INNER JOIN SALARY ON EMP.EMP_NO=SALARY.EMP_NO WHERE SALARY.SALARY=(SELECT MAX(SALARY) FROM SALARY);
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Fri Mar 28, 2008 11:22 am    Post subject:
Reply with quote

Quote:
Approach 2

Probably you well get all the rows...
How will MAX fn be in effect?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Fri Mar 28, 2008 11:26 am    Post subject:
Reply with quote

What Richa Jain suggested is as same as Abi's query...
Back to top
View user's profile Send private message
Richa Jain

New User


Joined: 18 Mar 2008
Posts: 35
Location: Gurgaon

PostPosted: Fri Mar 28, 2008 11:32 am    Post subject:
Reply with quote

Gnanas

Quote:

What Richa Jain suggested is as same as Abi's query...


    Since chiru_sh asked for JOIN and MAX function so i suggested the same
Back to top
View user's profile Send private message
abiyn

New User


Joined: 25 Mar 2008
Posts: 12
Location: Chennai

PostPosted: Fri Mar 28, 2008 11:36 am    Post subject:
Reply with quote

chiru_sh,


Approach 1 will work.

Approach 2 will not work. Because Group by function should be used when we select fields along with the aggregate function.
Correct me if i am wrong.

Thank You
Back to top
View user's profile Send private message
Richa Jain

New User


Joined: 18 Mar 2008
Posts: 35
Location: Gurgaon

PostPosted: Fri Mar 28, 2008 12:33 pm    Post subject:
Reply with quote

abiyn

You are correct. Aggregate functions cannot work without 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
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us