title: SQL SELECT MAX() is returning multiple records
body: <p>I am trying to write a SQL query that brings back record with the MAX ID2 and MAX DATETIME</p>
  
  <p>In my current result,</p>
  
  <p>There are records with the same nmae and special_number. They only differ by ID2 and Date_Time. The special_number should be unique and no duplicates. if there are duplicate special_number, select the record with the max ID2 and date_time</p>
  
  <p>For the duplicates (there are alot of them), I need to only select the record with the MAx ID2 and DATE_TIME. 
  For example, ADDEN CROSS, I need to only bring back the ADDEN CROSS with ID2 = 333 and  2017-05-05.</p>
  
  <p>I tried MAX but it did not work</p>
  
  <pre><code>SELECT DISTINCT ID, NAME, MAX(ID2), SPECIAL_NUMBER and DATE_TIME
  FROm TABLE1
  WHERE DATE_TIME BETWEEN @START_DATE and @END_DATE
  GROUP BY ID, ID2, SPECIAL_NUMBER, DATE_TIME
  </code></pre>
  
  <p>Any help is appreciated. Thanks</p>
  
  <p>My Current Results</p>
  
  <pre><code>ID      NAME        ID2     SPECIAL_NUMBER  DATE_TIME   
  123     JOE SMITH   456     D123            2017-06-05
  123     JOE SMITH   455     D123            2017-06-04
  124     JIM DONE    111     E111            2017-06-02
  122     ME TOO      222     G111            2017-06-01
  146     ADDEN CROSS 333     R555            2017-05-05
  146     ADDEN CROSS 332     R555            2017-05-04
  146     ADDEN CROSS 331     R555            2017-05-02
  </code></pre>
  
  <p>My Expected Results</p>
  
  <pre><code>ID      NAME        ID2     SPECIAL_NUMBER  DATE_TIME   
  123     JOE SMITH   456     D123            2017-06-05
  124     JIM DONE    111     E111            2017-06-02
  122     ME TOO      222     G111            2017-06-01
  146     ADDEN CROSS 333     R555            2017-05-05
  </code></pre>