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.
deleydSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT 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

Corrected per Paul's comment below.  Working off of an iPhone 4S has some limitations.

For an image and code heavy tutorial check out my article SQL Server GROUP BY Solutions
http://www.experts-exchange.com/articles/12312/SQL-Server-GROUP-BY-Solutions.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Group by t1.name
Order by t1.name

Jim forgot the ".name" in the above comment

No points pl
0
deleydSoftware EngineerAuthor Commented:
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."
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

deleydSoftware EngineerAuthor Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
PortletPaulfreelancerCommented:
Please UNLEARN this, immediately.

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

you absolutely never ever ever need to use DISTINCT when you are using GROUP BY
0
PortletPaulfreelancerCommented:
You have learned that using GROUP BY requires that all "non-aggregating" columns must be listed in the GROUP BY clause.

However you want not only the MAX(score) but now want another column from the row containing that max() value. Above Jim has given you one approach (using the group by query as a subquery that you join to the main table) or you can use the ROW_NUMBER() function like this:

select *
from table1 t1
inner join (
  select id, score, color
 , row_number() over(partition by id order by score DESC) as rn
  from table2
  ) t2 on t1.id = t2.id and t2.rn = 1
 
This query produces:
| ID |  Name | id | score | color | rn |
|----|-------|----|-------|-------|----|
|  1 | David |  1 |    90 | Green |  1 |
|  2 |   Ted |  2 |    75 |   Red |  1 |
|  3 | Susan |  3 |    77 | Green |  1 |

Open in new window


Note this approach always supplies just a single row even if there are 2 rows in table2 with a score of 90 for David.  

NB: It is the order within the OVER( .... ORDER BY ... DESC) which controls which row is given a value of 1.
The partition within OVER(PARTITION BY ... ) controls how many times the number restarts at 1

{+edits, sorry}
details
    CREATE TABLE Table1
        ([ID] int, [Name] varchar(50))
    ;
        
    INSERT INTO Table1
        ([ID], [Name])
    VALUES
        (1, 'David'),
        (2, 'Ted'),
        (3, 'Susan')
    ;
    
    CREATE TABLE Table2
        ([ID] int, [Score] int, [Color] varchar(5))
    ;
        
    INSERT INTO Table2
        ([ID], [Score], [Color])
    VALUES
        (1, 89, 'Red'),
        (1, 90, 'Green'),
        (1, 90, 'Blue'),
        (2, 75, 'Red'),
        (3, 77, 'Green')
    ;
    
**Query 1**:

    select *
    from table1 t1
    inner join (
      select id, score, color
      , row_number() over(partition by id order by score DESC) as rn
      from table2
      ) t2 on t1.id = t2.id and t2.rn = 1
      

**[Results][2]**:
    | ID |  Name | id | score | color | rn |
    |----|-------|----|-------|-------|----|
    |  1 | David |  1 |    90 | Green |  1 |
    |  2 |   Ted |  2 |    75 |   Red |  1 |
    |  3 | Susan |  3 |    77 | Green |  1 |

  [1]: http://sqlfiddle.com/#!3/601bd/2
  [2]: http://sqlfiddle.com/#!3/601bd/2/0

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

One more way to get this thing

SELECT A.NAME,B.SC Score ,b.COLOR FROM
Table1 A
INNER JOIN
(
SELECT A.ID,MAX(COLOR) COLOR,B.SC FROM Table2 A
INNER JOIN
(SELECT ID NU,MAX(SCORE) SC FROM Table2 GROUP BY ID) B
ON A.ID=B.NU AND A.Score=B.SC
GROUP BY A.ID,B.SC
) B ON A.ID=B.ID

Open in new window

0
deleydSoftware EngineerAuthor Commented:
Sorry I should have made the 2nd part a separate question. Thank you very much everyone! (Wow it's quickly getting complex!)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.