title: SQL - Return first non NULL Value after RANK() or ROW_NUMBER()
body: <p>Question on the following : </p>
  
  <p>I have a table (Client) that holds multiple records with the same username (unfortunately it's a limitation of the front end of the software).  When I join to that table on username, it obviously returns multiple rows.  Typically I use ROW_NUMBER(), partition by the username and order on a field I want and then join to limit the result set to one and return the desired columns and this has worked well in the past.  However, I'm attempting to use this in a way I haven't before... I'm partitioning by username, and ordering by the date the record was last updated in an effort to return a third field, in this case address.  Observe the following table results : </p>
  
  <pre><code>Username|UpdatedDate|Address
  JSmith  |10-10-14   |NULL
  JSmith  |05-24-14   |1 Main Street
  </code></pre>
  
  <p>The object is to return the most up to date record but if the latest record doesn't contain the Address, to grab the address from the previous record.</p>
  
  <p>Is this possible?</p>
  
  <p>EDIT : </p>
  
  <p>@Gordon...perhaps I'm misunderstanding something but in the first approach, the last updated date isn't taken into account when ordering. So if the table looks like : </p>
  
  <pre><code>JSmith|10-10-14|1 Main
  JSmith|04-20-14|1 Main 
  JSmith|01-10-13|5 Main
  </code></pre>
  
  <p>It will return 1 for all rows.  The ultimate goal is that if there only one record, to grab that one record.  If there is multiple records, to grab the most up to date record, but then if the address is NULL, grab the the next most up to date record with an address.</p>