title: SQL statement to determine primary computer for an assigned user based on login count
body: <p>I'm trying to have a SQL statement to determine primary computer for an assigned user based on login count. I have data and trying to determine the correct logic. </p>
  
  <ol>
  <li>Users can have multiple machines, but only one primary.</li>
  <li>The highest number of logons becomes the primary machine.</li>
  <li>Win7 and Win10 assigned to the same user with most logons are both primary machines.</li>
  </ol>
  
  <p>The data:</p>
  
  <pre><code>PreviouslyloggedHistory | LogonCount | ComputerName |  OS  | AssetAssigned
  user1                   |     31     |  computer1   |  W7  | user1
  user5                   |     1      |  computer1   |  W7  | user1
  user2                   |     1      |  computer2   |  W7  | user2  
  user2                   |     28     |  computer2   |  W7  | user2  
  user3                   |     1      |  computer2   |  W7  | user2 
  user4                   |     1      |  computer3   |  W10 | user2
  user5                   |     1      |  computer3   |  W10 | user2
  user2                   |     3      |  computer2   |  W7  | user2
  user5                   |     1      |  computer4   |  W10 | user6
  user6                   |     1      |  computer4   |  W10 | user6
  user7                   |     1      |  computer4   |  W10 | user6
  user6                   |     3      |  computer5   |  W10 | user6
  user5                   |     1      |  computer4   |  W10 | user6
  user6                   |     1      |  computer4   |  W10 | user6
  user7                   |     1      |  computer4   |  W10 | user6
  user5                   |     1      |  computer4   |  W10 | user6
  user6                   |    22      |  computer6   |  W7  | user6
  user6                   |     1      |  computer5   |  W10 | user6
  user6                   |     1      |  computer4   |  W10 | user6
  user7                   |     1      |  computer4   |  W10 | user6
  user6                   |     7      |  computer5   |  W10 | user6
  </code></pre>
  
  <p>Here's a query I have tried. I still get a lot of results where machines are marked as primary for more then 1 machine of the same OS.  I cannot figure out how to filter out low logon counts from the query for the same machine name.</p>
  
  <pre><code>
  select *,
  CASE
   WHEN [PreviouslyloggedHistory] = [AssetAssigned] THEN 'Primary'
   WHEN [PreviouslyloggedHistory] != [AssetAssigned] THEN 'Secondary'
   ELSE 'Unknown'
  End 'TagIt'
  FROM TABLE
  ORDER BY [AssetAssigned] DESC
  </code></pre>