Doubt with Join & Max function

chiru_sh · New User Joined: 21 Feb 2008 Posts: 14 Location: Mumbai

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.

abiyn · New User Joined: 25 Mar 2008 Posts: 12 Location: Chennai

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

chiru_sh · New User Joined: 21 Feb 2008 Posts: 14 Location: Mumbai

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.

Richa Jain · New User Joined: 18 Mar 2008 Posts: 34 Location: Chennai

Try this...

Gnanas SNG · Posted: Fri Mar 28, 2008 11:22 am Post subject:

Gnanas SNG · Posted: Fri Mar 28, 2008 11:26 am Post subject:

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

Richa Jain · New User Joined: 18 Mar 2008 Posts: 34 Location: Chennai

Gnanas

abiyn · New User Joined: 25 Mar 2008 Posts: 12 Location: Chennai

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

Richa Jain · New User Joined: 18 Mar 2008 Posts: 34 Location: Chennai

abiyn

You are correct. Aggregate functions cannot work without Group by clause.