title: Get top 1 row of each group
body: <p>I have a table which I want to get the latest entry for each group. Here's the table:</p>
  
  <p><code>DocumentStatusLogs</code> Table</p>
  
  <pre><code>|ID| DocumentID | Status | DateCreated |
  | 2| 1          | S1     | 7/29/2011   |
  | 3| 1          | S2     | 7/30/2011   |
  | 6| 1          | S1     | 8/02/2011   |
  | 1| 2          | S1     | 7/28/2011   |
  | 4| 2          | S2     | 7/30/2011   |
  | 5| 2          | S3     | 8/01/2011   |
  | 6| 3          | S1     | 8/02/2011   |
  </code></pre>
  
  <p>The table will be grouped by <code>DocumentID</code> and sorted by <code>DateCreated</code> in descending order. For each <code>DocumentID</code>, I want to get the latest status. </p>
  
  <p>My preferred output:</p>
  
  <pre><code>| DocumentID | Status | DateCreated |
  | 1          | S1     | 8/02/2011   |
  | 2          | S3     | 8/01/2011   |
  | 3          | S1     | 8/02/2011   |
  </code></pre>
  
  <ul>
  <li><p>Is there any aggregate function to get only the top from each group? See pseudo-code <code>GetOnlyTheTop</code> below:</p>
  
  <pre><code>SELECT
    DocumentID,
    GetOnlyTheTop(Status),
    GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC
  </code></pre></li>
  <li><p>If such function doesn't exist, is there any way I can achieve the output I want?</p></li>
  <li>Or at the first place, could this be caused by unnormalized database? I'm thinking, since what I'm looking for is just one row, should that <code>status</code> also be located in the parent table?</li>
  </ul>
  
  <p>Please see the parent table for more information:</p>
  
  <p>Current <code>Documents</code> Table</p>
  
  <pre><code>| DocumentID | Title  | Content  | DateCreated |
  | 1          | TitleA | ...      | ...         |
  | 2          | TitleB | ...      | ...         |
  | 3          | TitleC | ...      | ...         |
  </code></pre>
  
  <p>Should the parent table be like this so that I can easily access its status?</p>
  
  <pre><code>| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
  | 1          | TitleA | ...      | ...         | s1            |
  | 2          | TitleB | ...      | ...         | s3            |
  | 3          | TitleC | ...      | ...         | s1            |
  </code></pre>
  
  <p><strong>UPDATE</strong>
  I just learned how to use "apply" which makes it easier to address such problems.</p>