Several MAX values based on another column value

Question

I'm trying to write a SQL query in MS ACCESS and I've narrowed it down to the table below, but can't seem to get the last thing right without making several extremely large querys.

Here's the strucuture of thetable I'm trying to query:

enter image description here

The results I want: MemberId and year where memberId had most visits in that year.(That is which memberId had most visits 2014, which had most visits 2015 etc..and I also want the relevant year to be shown in the result)

Thanks!

this group by should work . select memberId, Year, max(totalVisits) from myTable group by memberId, Year — radar, Jan 12 '15 at 18:47
My Query only manages to get the max value for one year, so it's kinda useless. I tried your query now radar, problem is it returns several entries per year, I want only the one with highest visits for every year — user3284549, Jan 12 '15 at 18:53
Managed to solve it with help of another question link: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1 — user3284549, Jan 12 '15 at 19:04

score 0 · Accepted Answer · answered Jan 12 '15 at 19:02

Sounds like you need to determine MAX(Visits) by year in a subquery, then JOIN to that:

SELECT a.*,b.Max_Visits
FROM YourTable a
JOIN (SELECT Year,MAX(Visits) AS Max_Visits
      FROM YourTable
      GROUP BY Year
     ) b
 ON a.Year = b.Year
 AND a.Visits = b.Max_Visits

If you want to see all members and not just those that had the most visits per year, you can change from JOIN to LEFT JOIN

If there's a tie, this returns both members.

Several MAX values based on another column value

1 Answers1