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:
1 2 3 4 5 6 7 | +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | record_id | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | NO | | NULL | | | status | varchar(20) | NO | | NULL | | +-----------+-------------+------+-----+---------+----------------+ |
And records in ‘Posts’ table are:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | +-----------+---------+----------+ | record_id | post_id | status | +-----------+---------+----------+ | 1 | 1 | new | | 2 | 1 | draft | | 3 | 1 | submitted| | 4 | 2 | new | | 5 | 2 | draft | | 6 | 3 | new | | 7 | 4 | new | | 8 | 4 | draft | | 9 | 4 | submitted| | 10 | 4 | updated | +-----------+---------+----------+ |
Now we want to retrieve the last record for each post. The final result should be:
1 2 3 4 5 6 7 8 | +-----------+---------+----------+ | record_id | post_id | status | +-----------+---------+----------+ | 3 | 1 | submitted| | 5 | 2 | draft | | 6 | 3 | new | | 10 | 4 | updated | +-----------+---------+----------+ |
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:
1 2 3 4 | SELECT p1.* FROM Posts p1 LEFT JOIN Posts p2 ON (p1.post_id = p2.post_id AND p1.record_id < p2.record_id) WHERE p2.record_id IS NULL; |
Now all is done, happy coding guys!