title: Need a SQL query to get a single, max date when the max date is not unique
body: <p>My apologies if this has been asked and wasn't able to find this.  I've searched for a long time, with no luck.</p>
  
  <p>My table is named RESULT -</p>
  
  <pre><code>DATE                     TEST_NUM   RESULT_NUM
  11/16/2010 09:27:11 AM   123456      123111      
  11/16/2010 09:27:11 AM   123456      123222
  11/16/2010 09:27:11 AM   123456      123333
  </code></pre>
  
  <p>For a given TEST_NUM, I only want to return the max date just one time, and it doesn't matter what the other column entries are -</p>
  
  <p>11/16/2010 09:27:11 AM   123456      123111 </p>
  
  <pre><code>SELECT  RESULT.DATE,
          RESULT.RESULT_NUM,
          RESULT.TEST_NUM
   FROM RESULT
   WHERE RESULT.TEST_NUM = 123456 
   AND RESULT.DATE =  (SELECT  MAX(R1.DATE)
                        FROM RESULT R1
                        WHERE r1.TEST_NUM = RESULT.TEST_NUM)
  </code></pre>
  
  <p>But as you guessed, I don't get 1 result - I get all three.  I've tried everything!  Please help this newbie!  </p>