title: How to select a row based on a max count? (using standard sql)
body: <p>I have a table:</p>
  
  <p>ComputerID, UserID, LoginDate</p>
  
  <p>I need a select statement that returns:<br>
  ComputerID, UserID, LoginCount</p>
  
  <p>For <strong>all</strong> computers, but for each computer, showing <strong>just the one user that logged on most often to that computer</strong>.  (If there is a tie, I guess I would want to just arbitrarily pick one of the users....so this would seem to indicate I need a TOP 1 somewhere.)</p>
  
  <p>(This is in ms-access, so can't use vendor specific functionality).</p>
  
  <h2>Solution (slight fix of JBrooks answer)</h2>
  
  <pre><code>select main.*
  from (select ComputerID, UserID, count(1) as cnt
      from ComputerLoginHistory
      group by ComputerID, UserID) as main
             inner join (select ComputerID, max(cnt) As  maxCnt
                              from 
                                      (select ComputerID, UserID, count(1) as cnt
                                       from ComputerLoginHistory
                                       group by ComputerID, UserID) as Counts
                              group by ComputerID)
                            as maxes
  on main.ComputerID = maxes.ComputerID
  and main.cnt = maxes.maxCnt
  </code></pre>
  
  <p>To handle the situation where >1 user may have the same loginCount for a given computer, about all I can think of to wrap another select around this, selecting the Max(UserID).....so you are basically just arbitrarily picking one of them.  That's what I've done in this example, where I am pulling back the <strong>most recent</strong> user, rather than the most active user:</p>
  
  <pre><code>Select ComputerID, Max(xUserID) As UserID, MaxLoginDate
  FROM
  (
  SELECT main.ComputerID, main.UserID as xUserID, main.MaxLoginDate
  FROM [select ComputerID, UserID, Max(LoginDate) as MaxLoginDate
      from ComputerLoginHistory
      group by ComputerID, UserID]. AS main 
       INNER JOIN [select ComputerID, Max(MaxLoginDate) As MaxLogin
                              from 
                                      (select ComputerID, UserID, Max(LoginDate) as MaxLoginDate
                                       from ComputerLoginHistory
                                       group by ComputerID, UserID) as Counts
                              group by ComputerID]. AS maxes ON (main.MaxLoginDate = maxes.MaxLogin) AND (main.ComputerID = maxes.ComputerID)
  )
  GROUP BY ComputerID, MaxLoginDate
  ORDER BY ComputerID
  </code></pre>