Can't get the results I want using max

[roblasch]

I have a table with three fields. Name, points, and year. It holds the number pf points scored by each player for multiple years. I am trying to return the top scorer for each year. It seems simple, but I can't seem to get it.

select Name, max(points) from tblPoints group by year

is what I want to do. But if I also group by Name then it returns much more than just the single highest scorer. What am I missing? I know this should be simple.

 

[balves]

I think this will work...

SELECT A.Name, A.MaxNamePts, A.Year
FROM (SELECT Name, Year, Max(Points) as MaxNamePts
FROM tblPoints GROUP BY Name, Year) A
INNER JOIN
(SELECT Year, Max(Points) as MaxYearPts
FROM tblPoints GROUP BY Year) B
ON
A.MaxNamePts = B.MaxYearPts AND
A.Year = B.Year

 

[hirick]

Try this. It looks simpler:

Select A.Name, A.Points, A.Year
From tblPoints A
Inner Join (Select Max(Points) As MaxPoints, Year
From tblPoints
Group By Year) B
On B.MaxPoints = A.Points And B.Year=A.Year
Order By A.Year

If multiple people got the same number of points, you will get a record for each