title: SQL Server Select the most recent record only
body: <p>I have a table tat looks like this:</p>
  
  <p>Table1</p>
  
  <pre><code>ID Date       TermDate Cancel
  1  20140101   99999999 20140102
  1  20140101   20130102 20140102
  
  2  20140108   20130102 20140102
  2  20140101   99999999 20140102
  
  3  20140101   99999999 20140102
  3  20140101   99999999 20140102
  3  20140101   99999999 20140709
  </code></pre>
  
  <p>What I want to do is, to group by each ID and select only the most recent record.
  First I need to check the Date column, from there if a record is more recent then I do not need to check the other columns, in this case, it is ID #2.</p>
  
  <p>If the Date column is the same for same ID, then I want to compare the Cancel column, whichever is the most recent then that record should be shown only, in this case it is ID 3.</p>
  
  <p>If both Date and Cancel columns are the same, then I need to compare the TermDate column and the most recent should be displayed, in this case it is ID #1. All columns are int type.</p>
  
  <p>Basically the result should be:</p>
  
  <pre><code>ID Date       TermDate Cancel
  1  20140101   99999999 20140102
  2  20140108   20130102 20140102
  3  20140101   99999999 20140709
  </code></pre>
  
  <p>I am not the best person with self join tables and really not sure how to get started with this... what I currently do is: I copy the table into Excel file and then do the job manually... 
  I am thinking to use CASE Statement but really do not know how to convert my logic to SQL proper code, here is my pseducode:</p>
  
  <pre><code>SELECT (CASE WHEN Date &gt; DATE THEN SELECT the greater Date 
          CASE WHEN Date = Date THEN SELECT the grater Cancel
          ELSE WHEN Date = Date AND Cancel = Cancel THEN SELECT the grater TermDate END)
  FROM Table1
  GROUP BY ID
  </code></pre>
  
  <p>I don't even know how to make the table look like the above with case statement.</p>
  
  <p>Please let me know what is the best way to do this... I searched online with no help.</p>