title: What's the smartest way to ignore nulls and show a single value for a column that is part of a bigger query?
body: <p>I'm working with a table that looks something like this:</p>
  
  <pre><code>|   |  Name | CaseID | UsrID | DL_NO |   SSN   | Address     | DateSeen   |
  |---|:-----:|:------:|:-----:|:-----:|:-------:|-------------|------------|
  | 1 | Smith |  AB190 | 88885 |       | 1234567 | 222 Side Rd | 01/01/2020 |
  | 2 | Smith |  AB186 | 88885 | B0938 |         |             | 10/01/2019 |
  | 3 | Smith |  AB170 | 88885 |       | 1234567 | 123 Side Rd | 09/01/2019 |
  | 4 | Smith |  AB168 | 88885 | B0938 |         | 123 Road St | 03/05/2019 |
  | 5 | Smith |  AB132 | 88885 | B0938 | 1234567 |             | 03/01/2019 |
  | 6 | Smith |  AB102 | 88885 | B0938 | 1234567 | 123 Road St | 02/01/2019 |
  </code></pre>
  
  <p>I'm having trouble understanding how to properly include data that updates/occasionally has missing bits. </p>
  
  <p>What I'd like to see are the most up-to-date, non-null values for each column:</p>
  
  <pre><code>|   |  Name | NumOfCases | UsrID | DL_NO |   SSN   | Address     |
  |---|:-----:|:----------:|:-----:|:-----:|:-------:|-------------|
  | 1 | Smith |      6     | 88885 | B0938 | 1234567 | 222 Side Rd |
  </code></pre>
  
  <p>I was using this: </p>
  
  <pre><code>SELECT TOP 50 Name, UsrID, COUNT(DISTINCT CaseID) as NumofCases
  FROM People
  WHERE DateSeen between 01/31/2019 and 10/02/2019
  GROUP BY Name, UsrID
  ORDER BY DateSeen desc
  </code></pre>
  
  <p>Returns</p>
  
  <pre><code>|   |  Name | UsrID | NumofCases |
  |---|:-----:|-------|:----------:|
  | 1 | Smith | 88885 |      6     |
  </code></pre>
  
  <p>This was working okay until I realized I have a use for the other fields. </p>
  
  <p>When I attempt a similar query with the additional columns, the forced GROUP BY obviously ruins the "NumofCases" count. </p>
  
  <p>Even a CROSS APPLY where I "SELECT TOP 1" requires a group by. </p>
  
  <p>Any thoughts?</p>