group by failure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

group by failure

Author

Topic

dcsonkanet
Starting Member

2 Posts

Posted - 2008-03-28 : 20:03:05

Hello, if you can help, I would be so grateful. I see many here are very smart with SQL.

I am trying to figure out (what might be a simple thing) a process with group by and Max() function.

I have two tables:

STAFF_INFO (STAFF_ID = PK, STAFF_NAME, SALARY, DEPT_ID = FK)
1 .. David .. $100 .. 1 (dept_id)
2 .. John .. $200 .. 2 (dept_id)
3 .. Mary .. $300 .. 3 (dept_id)
4 .. James .. $150 .. 1 (dept_id)
5 .. Jason .. $250 .. 2 (dept_id)
6 .. George .. $350 .. 3 (dept_id)

DEPT_INFO (DEPT_ID = PK, DEPT_NAME)
1 .. History
2 .. Math
3 .. Science

What I'd like to do is have the name of the staff member, with the highest salary in each department.

Like this:
James History $150
Jason Math $250
George Science $350

Now - this following works, obviously:
SELECT d.DEPT_NAME, MAX(s.SALARY)
FROM STAFF_INFO s
JOIN DEPT_INFO d ON d.DEPT_ID = s.DEPT_ID
GROUP BY d.DEPT_NAME

I get:
History $150
Math $250
Science $350

But if I add staff name like this:
SELECT d.DEPT_NAME, s.STAFF_NAME, MAX(s.SALARY)
FROM STAFF_INFO s
JOIN DEPT_INFO d ON d.DEPT_ID = s.DEPT_ID
GROUP BY d.DEPT_NAME, s.STAFF_NAME

I get everybody, instead of the top one for each department.

I've tried a bunch of things, but nothing works.. please help if you can shine light on this. I would love to understand how the process would work! :)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-03-28 : 20:43:31

You need to do this with a subquery. Put the query that you wrote which works into another query as a derived table. Something like this:

SELECT SI.Staff_Name, T.DeptName, T.Sal
FROM (
SELECT d.DEPT_ID, d.DEPT_NAME, MAX(s.SALARY) as Sal
FROM STAFF_INFO s
JOIN DEPT_INFO d ON d.DEPT_ID = s.DEPT_ID
GROUP BY d.DEPT_ID, d.DEPT_NAME
) as T
JOIN STAFF_INFO as SI ON SI.DEPT_ID = T.DEPT_ID and SI.SALARY = T.SALARY

Of course, you're going to have to figure out what to do with the possibility that more than one person has the same max salary within their department, but this will show them all.

---------------------------
EmeraldCityDomains.com

dcsonkanet
Starting Member

2 Posts

Posted - 2008-03-28 : 23:02:03

quote:
Originally posted by AjarnMark

You need to do this with a subquery. Put the query that you wrote which works into another query as a derived table. Something like this:

SELECT SI.Staff_Name, T.DeptName, T.Sal
FROM (
SELECT d.DEPT_ID, d.DEPT_NAME, MAX(s.SALARY) as Sal
FROM STAFF_INFO s
JOIN DEPT_INFO d ON d.DEPT_ID = s.DEPT_ID
GROUP BY d.DEPT_ID, d.DEPT_NAME
) as T
JOIN STAFF_INFO as SI ON SI.DEPT_ID = T.DEPT_ID and SI.SALARY = T.SALARY

Of course, you're going to have to figure out what to do with the possibility that more than one person has the same max salary within their department, but this will show them all.

---------------------------
EmeraldCityDomains.com

I tried working with your idea, but I still can't get your SQL to work (corrected for column name typo)

Is the idea, to have the derived table a list of all of the values, and use the outer SELECT to pull only the rows with max salaries from the derived table?

I thought something like this:
SELECT x.STAFF_NAME, x.DEPT_NAME, MAX(x.SALARY)
FROM (
SELECT s.STAFF_NAME, d.DEPT_NAME, d.DEPT_ID, s.SALARY
FROM STAFF_INFO s
JOIN DEPT_INFO d ON d.DEPT_ID = s.DEPT_ID
) x
GROUP BY x.STAFF_NAME, x.DEPT_NAME

But then, it just seems to display the same info again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 02:16:28

use ROW_NUMBER() approach:-

SELECT t.STAFF_NAME,t.DEPT_NAME,t.SALARY
FROM
(SELECT ROW_NUMBER() OVER ( PARTITION BY s.DEPT_ID ORDER BY s.SALARY DESC) AS Seq,
s.STAFF_ID,s.STAFF_NAME,s.SALARY,s.DEPT_ID,d.DEPT_NAME
FROM STAFF_INFO s 
INNER JOIN DEPT_INFO d
ON d.DEPT_ID=s.DEPT_ID
)t
WHERE t.Seq=1

or use like this:-

SELECT s.*,d.DEPT_NAME
FROM STAFF_INFO s
INNER JOIN (SELECT DEPT_ID,MAX(SALARY) as MaxSal
            FROM STAFF_INFO
            GROUP BY DEPT_ID)t
ON t.DEPT_ID=s.DEPT_ID
AND t.MaxSal=s.SALARY
INNER JOIN DEPT_INFO d
ON d.DEPT_ID=s.DEPT_ID

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-29 : 05:03:59

quote:

Originally posted by visakh16

use ROW_NUMBER() approach:-

SELECT t.STAFF_NAME,t.DEPT_NAME,t.SALARY
FROM
(SELECT ROW_NUMBER() OVER ( PARTITION BY s.DEPT_ID ORDER BY s.SALARY DESC) AS Seq,
s.STAFF_ID,s.STAFF_NAME,s.SALARY,s.DEPT_ID,d.DEPT_NAME
FROM STAFF_INFO s 
INNER JOIN DEPT_INFO d
ON d.DEPT_ID=s.DEPT_ID
)t
WHERE t.Seq=1

But if any two staffs have the same salary which are the highest salary in a particular department then you will have to use the dense_rank() approach to get the ids of both the staff.

SELECT t.STAFF_NAME,t.DEPT_NAME,t.SALARY
FROM
(SELECT DENSE_RANK() OVER ( PARTITION BY s.DEPT_ID ORDER BY s.SALARY DESC) AS Seq,
s.STAFF_ID,s.STAFF_NAME,s.SALARY,s.DEPT_ID,d.DEPT_NAME
FROM STAFF_INFO s
INNER JOIN DEPT_INFO d
ON d.DEPT_ID=s.DEPT_ID
)t
WHERE t.Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources