Extremely tough sql query 2

[xpblueScreenOfDeath]

Dept EmployeeName Score
---- ------------ -----
GH John Smith 56
GH Joe Blow 99
JK Max Brown 78
HJ Sara Ly 34
HJ Larry Smith 3

In a sql query, How do I select an employee with the highest score from each department? Resultset from the above dataset should look like this:

Dept EmployeeName Score
---- ------------ -----
GH Joe Blow 99
JK Max Brown 78
HJ Sara Ly 34

 

[Kavius]

I would suggest using max in a sub query.

 

[rac2]

Yes that is a bit difficult.

It is straigtforward to find the maximum score for each department.

SELECT Dept, MAX(Score)
FROM yeOldeEmployeeTable
GROUP BY Dept

But how to match that up with the employee name?

You could join that result to the employee table on the score. Which would work pretty good if the scores are unique within a department. But in this case there are probably two employees with the same score in some departments. I know in my two-man department there are two people with scores of 100, since we both attend scrupulously to the needs and whims of our supervisor. But that is another story.

SELECT a.Dept, a.EmployeeName, b.Score
FROM yeOldeEmployeeTable a
JOIN (
      SELECT Dept, MAX(Score) AS Score
      FROM yeOldeEmployeeTable
      GROUP BY Dept
      ) b ON b.Score = a.Score
             AND b.Dept = a.Dept

 

[r937]

a self-join works, even in databases (*cough*mysql*cough*) that don't support subqueries!!

create table minions
( Dept char(2)
, EmployeeName varchar(21)
, Score tinyint
);

insert into minions values
 ('GH', 'John Smith', 56)
,('GH', 'Joe Blow', 99)
,('JK', 'Max Brown', 78)
,('HJ', 'Sara Ly', 34)
,('HJ', 'Larry Smith', 3)
,('XX', 'rac2', 100)
,('XX', 'Good Emp #2', 100)
,('XX', 'Poor Emp #1', 99)
,('XX', 'Poor Emp #2', 99)
;
 
select t1.Dept
     , t1.EmployeeName 
     , t1.Score
  from minions as t1
inner
  join minions as t2
    on t1.Dept
     = t2.Dept
group
    by t1.Dept
     , t1.EmployeeName 
     , t1.Score
having t1.Score
     = max(t2.Score)           

results:

Dept  EmployeeName   Score
 GH   Joe Blow        99
 HJ   Sara Ly         34
 JK   Max Brown       78
 XX   Good Emp #2    100
 XX   [b]rac2[/b]           100

notice the self-join handles ties nicely, too

rudy

http://r937.com/

SQL Consulting

 

[Kavius]

I agree the self-join is probably faster. I would likely use a self-join myself, but for some reason I couldn't think of how to do it at the time.

mysql doesn't support sub-queries? I'm sticking to pgsql.

 

[r937]

mysql supports subqueries in 4.1, which is not yet in stable production status

but then, many queries written with subqueries can also be written as joins instead

rudy

http://r937.com/

SQL Consulting

 

[rac2]

Oh rudy! I like that. Your solution breaks new ground for me. I would have never thought of joining on a non-unique field. That must be OK here due to the GROUP BY. And a GROUP BY query without an aggregate, too. Good stuff.

 

[r937]

a self-join with a GROUP BY on all columns of one of the table copies is not that obscure, is it? i use them all the time in mysql

as the guys at mysql.com say, most subqueries can be rewritten as joins, and they be right

oh, and there was an aggregate, it was hiding in the HAVING clause



rudy

http://r937.com/

SQL Consulting