title: Select top one from left outer join
body: <p>Guys, I have a query where basically select the latest browser that our user used.</p>
  
  <p>here is our (simplified) table structure</p>
  
  <pre><code>HITS_TABLE
  ----------
  USERID
  BROWSER
  HITSDATE
  
  USER_TABLE
  ----------
  USERID
  USERNAME
  </code></pre>
  
  <p>and here is how I query the latest browser that our user used</p>
  
  <pre><code>SELECT U.*, H.BROWSER
  
  FROM USER_TABLE U
  
  CROSS APPLY 
    (SELECT TOP 1 BROWSER 
     FROM HITS_TABLE 
     WHERE HITS_TABLE.USERID = U.USERID
     ORDER BY HITS_TABLE.HITSDATE DESC
    )as H
  </code></pre>
  
  <p>The HITS_TABLE is just added several days ago. </p>
  
  <p>So, that query is just resulting users that visited our website after we added the HITS_TABLE, and eliminate the others.</p>
  
  <p>Here is the sample case</p>
  
  <pre><code>USER_TABLE
  -------------------
  USERID     USERNAME
  -------------------
  1          'Spolski'
  2          'Atwoord
  3          'Dixon'
  
  
  HITS_TABLE
  ------------------------------
  USERID     HITSDATE     BROWSER
  ------------------------------
  2          15/8/2009    'Firefox 3.5'
  1          16/8/2009    'IE 6'
  2          16/8/2009    'Chrome'
  </code></pre>
  
  <p>Here is the sample result</p>
  
  <pre><code>------------------------------
  USERID     USERNAME     BROWSER
  ------------------------------
  1          'Spolsky'    'IE 6'
  2          'Atwoord'    'Chrome'
  </code></pre>
  
  <p>But, I want to add other users with 'unknown' browser.
  Here is my desired result</p>
  
  <pre><code>------------------------------
  USERID     USERNAME     BROWSER
  ------------------------------
  1          'Spolsky'    'IE 6'
  2          'Atwoord'    'Chrome'
  3          'Dixon'      'Unknown'
  </code></pre>
  
  <p>I believe it could be achieved by LEFT OUTER JOIN.
  But I always had this: (I DO NOT want this result)</p>
  
  <pre><code>------------------------------
  USERID     USERNAME     BROWSER
  ------------------------------
  1          'Spolsky'    'IE 6'
  2          'Atwoord'    'Chrome'
  2          'Atwoord'    'Firefox 3.5'
  3          'Dixon'      'Unknown'
  </code></pre>
  
  <p>I hope my question is clear.</p>