title: SQL Server select distinct latest values
body: <p>I have a table that has a ton of rows (>10K). Most of the rows have duplicate <strong>role</strong> values associated with the <strong>username</strong>. </p>
  
  <p>What I am trying to do is select rows by distinct AND latest <strong>role</strong> added by <strong>request_id</strong>. 
  I almost have it, but the part that is kicking my tail is there are <code>null</code> values in some of the <strong>request_id</strong> fields because those requests were made before that column was added. I <strong>STILL</strong> need to include them in the <code>select statement</code> in case a user has not entered another request since the update.</p>
  
  <p>Here a example of my table structure:</p>
  
  <pre><code> id | uname  | role    | request_id
   0  | jsmith  | User   | null
   1  | jsmith  | Admin   | null
   2  | jsmith  | Dude    | null
   3  | jsmith  | Admin   | 56
   4  | jsmith  | Dude    | 56
   5  | jsmith  | Admin   | 57
   6  | jsmith  | Dude    | 57
  </code></pre>
  
  <p><strong><em>This would be the desired result:</em></strong></p>
  
  <pre><code>0  | jsmith  | User    | null
  5  | jsmith  | Admin   | 57
  6  | jsmith  | Dude    | 57
  </code></pre>
  
  <p>Here are the statements I've tried so far:</p>
  
  <h1>Join</h1>
  
  <pre><code>select distinct a.uname, a.role, a.request_id from (
      select * from  das_table 
      ) 
  b join das_table a on b.role = a.role and b.request_id = a.request_id
  where a.uname = 'jsmith'
  </code></pre>
  
  <p><strong>Result:</strong> This eliminates the rows with <strong>request_id</strong> = <code>NULL</code></p>
  
  <h1>MAX()</h1>
  
  <p>This didn't work for me I guess because <code>MAX()</code> doesn't count <code>null</code> values?</p>
  
  <pre><code>select distinct uname, role, max(request_id) as request_id from das_table where uname='jsmith'
  group by uname, role, request_id
  </code></pre>
  
  <h1>Similar questions I've looked at:</h1>
  
  <p>One caveat in my question that I think makes it different from the others I've researched is the <code>request_id</code> having the possibility of being null.</p>
  
  <p><a href="https://stackoverflow.com/questions/1918556/sql-select-distinct-values-from-1-column">Select distinct values from 1 column</a> | 
  <a href="https://stackoverflow.com/questions/13461902/sql-server-select-distinct-by-one-column-and-by-another-column-value">SQL Server : select distinct by one column and by another column value</a> | 
  <a href="https://stackoverflow.com/questions/2247401/sql-server-select-distinct-hell">SQL Server select distinct hell</a></p>