asked on

SQL select max score for each player

This is a massive query which I've condensed down to this simple problem hoping to find a simple answer.

We have a table of names, and a table of scores for each name. We want to select the maximum score for each name:

Table 1            Table 2

ID   Name          ID   Score
----------         ----------
1  | David         1    89
2  | Ted           1    90
3  | Susan         1    90
		   2    75
		   3    77

Open in new window

The current (somewhat horrific) way the query is currently done is to first create a temporary copy of Table 2, and then perform a SELECT:

SELECT * 
INTO Table2Copy
FROM Table2


SELECT DISTINCT NAME.id
      ,NAME.Name
      ,SCORE.Score
FROM Table1 as NAME
LEFT OUTER JOIN Table2 as SCORE on SCORE.id = NAME.id
	  AND SCORE.Score = (SELECT TOP 1 MAX(TMP.Score) FROM Table2Copy TMP WHERE TMP.ID = NAME.Id)

Open in new window

There's got to be a better way that doesn't involve making a temporary copy of Table 2.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Group by t1.name
Order by t1.name

Jim forgot the ".name" in the above comment

No points pl

deleyd

ASKER

Good so far. But now I have a problem when I try to add other columns, such as t1.id

SELECT distinct t1.id, t1.Name, MAX(t2.Score) as max_score
FROM Table1 t1
   JOIN Table2 t2 ON t1.id = t2.id
GROUP BY t1.name
ORDER BY t1.name

Open in new window

"Column 'Table1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

deleyd

ASKER

Table 1            Table 2

ID   Name          ID   Score  Color
----------         -----------------
1  | David         1    89     Red
2  | Ted           1    90     Green
3  | Susan         1    90     Blue
		   2    75     Red 
		   3    77     Green

Open in new window

Now the table gets larger, and I would like both the high score for each person, and the color corresponding to that high score.

SELECT distinct t1.id, t1.Name, MAX(t2.Score) as max_score, t2.Color
FROM Table1 t1
   JOIN Table2 t2 ON t1.id = t2.id
GROUP BY t1.name, t1.id, t2.Color
ORDER BY t1.name, t1.id, t2.Color

Open in new window

I'm getting a Cartesian Product of MAX score with all possible colors for that user.

Jim Horn

Ok, but keep in mind we've answered your question as asked, so let's not get too carried away with follow-on questions within this one.

Subquery to get the max score and color, main query to get the name. Give this a whirl..

SELECT t1.id, t2.score, t2.color
FROM Table1 t1
   JOIN (SELECT id, Max(Score) as max_score FROM Table2 GROUP BY id) t2max ON t1.id = t2max.id 
   JOIN Table2 t2 ON t2max.id = t2.id AND t2max.max_score = t2.score

Open in new window

PortletPaul

Please UNLEARN this, immediately.

select DISTINCT .....
from ...
GROUP BY ...

you absolutely never ever ever need to use DISTINCT when you are using GROUP BY

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deleyd

ASKER

Sorry I should have made the 2nd part a separate question. Thank you very much everyone! (Wow it's quickly getting complex!)