Databases
StackOverflow2013
Postsdbo
POGet top 1 row of each group
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet top 1 row of each group
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload