Efficient SQL query to get the EMPID

mpawankumar · New User Joined: 26 Sep 2007 Posts: 7 Location: Chennai

Hi,

Can any one please help me out in writing an efficient query to get the Employes earning max. salary in their own dept. using a sub queries..

Employee table has columns as below

EmpID EmpName DeptID Salary

Anuj Dhawan · Posted: Sun Jan 04, 2009 11:52 am

Hi,

Do you have some in-efficient solution with you, you are currently working on ?

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

How about trying this

Select Max(Salary),DeptID from Employee
Group by DeptID;

abhishek dadhichi · New User Joined: 19 Apr 2007 Posts: 37 Location: Bangalore

Hi Pawan,

Please try this:

sel emp_id,dept_id
from emp_table
where sal in
(sel max(sal) from emp_table);

Arun Raj · Posted: Mon Jan 05, 2009 3:33 pm

abhishek dadhichi,

The above SQL will not give a dept level summary as asked by the TS.

abhishek dadhichi · New User Joined: 19 Apr 2007 Posts: 37 Location: Bangalore

Hi,

Please try this one:

sel empName
from employee
where deptid in
(sel deptid,max(salary) from employee
group by 1);

Thanks,
Abhishek