easy sql question - group by 2

[Custom24]

Hi
Suppose I have a table that looks something like this

Name Dep Salary
-----------------------
Mark IT 40
Jim Sales 50
Jon IT 60
Sue Sales 70

I want to select the name of the person with the maximum salary in each department, their salary and the department they work for, ie

Name Dep Max(Salary)
-----------------------
Jon IT 60
Sue Sales 70


This does not work
Select Name, Max(Salary), Dep from Table Group by Dep;

because it produces the error "not a group by expression"

And neither does this
Select Name, Max(Salary), Dep from Table Group by Dep, Name;

because it basically returns the whole table

I can't find any help on this

Surely it is not that difficult?

Thanks
Mark

 

[ija]

Have not tried, but it can give you at least an inspiration:
[tt]select tab.name, tab.dep, tab.salary
from (
Select dep, Max(Salary) as sal
from Table
Group by Dep) as maxs,
table tab
where tab.dep = maxs.dep
and tab.salary = maxs.salary
[/tt]

 

[ThomVF]

You could try:

select name, salary, dep
from table,
where (salary, dep) in
( select max(salary), dep
from table
group by dep)

If more than one emp has a salary equial to the max for the dept, they will all show up.
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[ThomVF]

This also works:

select e.ename, e.sal, e.deptno
from emp e,
(select max(m.sal) sal,m.deptno
from emp m group by m.deptno) m
where m.sal = e.sal
and m.deptno = e.deptno


Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Custom24]

Thanks guys - we figured out yet another way of doing it as well

select name, dep, salary from table where salary||dep in(
select max(salary)||dep saldept from table group by dep);