My friend has a question, and there are A, B, C three people do games, record the first score, after dozens of games, the results are as follows:
ID Name Score
1 a 88
2 B 76
3 C 66
4 C 90
5 B 77
6 a 56
7 b 77
8 c 67
9 a 44
......
Of course there are still many
Require search A, B, C three people all the best results, and to list the best grade, the number ID number
Such as:
ID Name Score
1 a 88
5 B 77
4 C 90
I found that it is like a primary question, putting the eyes on the maximum, but then I feel that this focus is on the maximum value, but the ID number where the maximum is located.
Difficulties: Search for maximum
ID number
Exception: Everyone may have a few largest values
Brain: Start thinking about this MAX function:
SELECT MAX (Score), [Name] group by [name]
But this damn
How can I insert the ID number?
First-line hopes: use a child query:
SELECT [ID], [Name], SCORE
From table1 a
WHERE SCORE IN
(Select Max (B.score) AS MaxScore
From Table1 B
GROUP BY B. [Name])
However, this
The ID number is still more,
the reason
Since the search is based on the set of largest values, such as 77 this maximum, it may be B's maximum, but A and C may also have 77 this number, but not the maximum, so ...... Victory, finally added a condition, make the Name value in the maximum collection, compliant with the search Name value, [name], scorefrom table1 awhere score in
(Select Max (B.score) AS MaxScore
From Table1 B
Group by b. [Name] having a. [Name] = b. [Name]) The result is a smooth problem, which is often inconspicuous.
