Help with Group by clause

Sudar · #1 (**permalink**) 11-05-05, 02:33

I have two tables

First table

mstr_dept

Code:

  
  `deptid` int(11) NOT NULL primary key,
  `deptname` varchar(10) NOT NULL,

Second table

emp

Code:

  
  `empid` int(11) NOT NULL primary key,
  `deptid` int(11) refrences mstr_dept(deptid)
  `name` varchar(30) NOT NULL,
  `salary` int(11) NOT NULL default '0',

Now i have to select the maximum salary for each department

for which i can use the query

Code:

  
SELECT deptid, MAX(salary) from emp group by deptid

but now i want to know who is getting the maximum salary in each department. If i give the query

Code:

  
SELECT deptid, empid, MAX(salary) from emp group by deptid, empid

i am not getting the correct answer. So any one can help me..

sassermann · #2 (**permalink**) 11-14-05, 12:39

Try something like this:

select e.deptid, e.empid, e.name, e.salary from emp e, (SELECT deptid, MAX(salary) sal from emp group by deptid ) max_sal where e.deptid=max_sal.deptid and e.salary=max_sal.sal

Sudar · #3 (**permalink**) 11-15-05, 05:32

Thanks sassermann for the reply.....

Internet Services

Web Development

Digital Marketing

Consumer Tech