problem about select max.....group by clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem about select max.....group by clause

Author

Topic

deadfish
Starting Member

38 Posts

Posted - 2002-11-16 : 11:01:36

Hi. I have a problem on the "select max...group by" clause.

Here is the table about staffs' salary:

empid deptid salary
----------------------------
emp1 d1 5000
emp2 d1 2000
emp3 d1 9000
emp4 d2 7000
emp5 d2 6000

I would like to find out the max. salary with the empid in each department to produce the following result:

empid salary
--------------
emp3 9000
emp4 7000

However, I get fail in executing this:

select empid, max(salary) group by deptid

Any idea?

Thanks!

Edited by - deadfish on 11/16/2002 16:08:49

rharmon
Starting Member

41 Posts

Posted - 2002-11-16 : 16:42:10

try:
select deptid, empid, max(salary) from stafftable group by deptid, empid

or if it's possible to have the same employee in multiple departments:

select empid from stafftable inner join
(select deptid, max(salary) as ms from stafftable st
group by deptid) mst
on mst.deptid = stafftable.deptid and mst.salary = stafftable.salary

deadfish
Starting Member

38 Posts

Posted - 2002-11-17 : 05:17:26

The second one works fine! Thanks!

What about if I want to find the min salary and empid also?

I wrote the following:

select empid from stafftable inner join
(select deptid, min(salary) as mins from stafftable st
group by deptid) mst
on mst.deptid = stafftable.deptid and mst.salary = stafftable.salary

This would give me the record set containing the empid with the min. salary in each department.

How to join this record set to the record set containing the max. salary together?

depid maxsalary empid minsalary empid2
--------------------------------------
d1 9000 emp3 5000 emp1
d2 7000 emp4 6000 emp5

Edited by - deadfish on 11/17/2002 06:07:03

rharmon
Starting Member

41 Posts

Posted - 2002-11-17 : 18:30:12

Using derived tables:

select maxtable.deptid, maxtable.salary, maxtable.empid , mintable.salary, mintable.empid from

(select mst.deptid, empid, mst.salary from stafftable inner join
(select deptid, max(salary) as salary from stafftable st
group by deptid) mst
on mst.deptid = stafftable.deptid and mst.salary = stafftable.salary ) maxtable

inner join

(select mst.deptid, empid, mst.salary from stafftable inner join
(select deptid, min(salary) as salary from stafftable st
group by deptid) mst
on mst.deptid = stafftable.deptid and mst.salary = stafftable.salary ) mintable

on mintable.deptid = maxtable.deptid

rharmon
Starting Member

41 Posts

Posted - 2002-11-17 : 18:44:05

Of course the whole thing will come apart if two people in the same department show up with the sam max or min salary

deadfish
Starting Member

38 Posts

Posted - 2002-11-17 : 20:45:47

I've got what I want finally....thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources