title: Find most active or most recent userid by machineid
body: <p>I have a table with MachineID, UserID and Date columns.  Each day I insert/append into this table a record of which user logs onto which machine, so I end up with a daily historical log of users logged into machines.  I need to be able to extract from this the most active user for a machine (the UserID with the most rows against a MachineID), or in the case that two users are tied for "first place", the most recent of those two users.</p>
  
  <p>I'm running SQL 2012 Express.  Basically I've got to the point where I have the MachineID and UserID and the number of dates/rows for each combination of these, but I can't figure out how to get any further.</p>
  
  <p>Using the sample data set of:</p>
  
  <pre><code>[MachineID] [UserID] [Date]
  1   1   20190619
  1   1   20190620
  1   2   20190621
  2   3   20190618
  2   3   20190619
  2   4   20190620
  2   4   20190621
  </code></pre>
  
  <p>I've run the SQL:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT MachineID
      , UserID
      , COUNT(DTAdded) as [Logon Count]
  FROM MUHistory
  GROUP BY MachineID, UserID
  </code></pre>
  
  <p>and the result is:</p>
  
  <pre><code>[MachineID] [UserID]    [Logon Count]
  1   1   2
  1   2   1
  2   3   2
  2   4   2
  </code></pre>
  
  <p>However my desired result is:</p>
  
  <pre><code>[MachineID] [UserID]
  1   1
  2   4
  </code></pre>
  
  <p>Where on MachineID '1' the User '1' has logged on more than any other user, and on MachineID '2' UserID '3' and '4' have both logged on the same number of times, but of these UserID '4' has the most recent logon.</p>
  
  <p>Thanks in advance, I've had a search around but I can only find posts on getting one or the other result (most active or most recent) but not the two combined.</p>