Retrieving the last record in each group

Many times we need to retrieve the last record in each group while working with database. Here group is set of those rows which have an identical value of some column. Let us understand this with the given example below:

Our ‘Posts’ table structure is:

 And records in ‘Posts’ table are:

Now we want to retrieve the last record for each post. The final result should be:

To get the result many developers use query with ‘GROUP BY‘ but let me tell you that ‘GROUP BY‘ query suffers with poor performance while you have a large number of rows to obtain the result.

Just to have a look, I had around 1,115,503 rows and a ‘GROUP BY‘ query took 1 minute and 17.96 seconds to obtain the most recent record for a given post while the solution provided below performed in 0.31 seconds.

SOLUTION: To retrieve the last record in each group, query below works better using technique with LEFT JOIN:

Now all is done, happy coding guys!

You Might Interested In

Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.