In single SQL query itself we can retrieve the last record in each group. It will help us to perform action better, faster and simpler. In this post I will explain about you how to do it.
Here is my table structure for your reference.
[sql]
desc post_status;
+—————-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+—————-+————-+——+—–+———+—————-+
| post_status_id | int(11) | NO | PRI | NULL | auto_increment |
| post_id | int(11) | NO | | NULL | |
| status | varchar(25) | NO | | NULL | |
+—————-+————-+——+—–+———+—————-+
[/sql]
According to this table, It contains multiple status for single post.
[sql]
SELECT * FROM `post_status`;
+—————-+———+———-+
| post_status_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 |
+—————-+———+———-+
[/sql]
To get the last updated status of post using the following SQL Query:
[sql]
SELECT post_id, SUBSTRING_INDEX(GROUP_CONCAT(status ORDER BY post_status_id DESC),’,’,1) as status FROM `post_status` GROUP BY post_id;
+———+———-+
| post_id | status |
+———+———-+
| 1 | submitted|
| 2 | draft |
| 3 | new |
| 4 | updated |
+———+———-+
[/sql]
Explanation for the above SQL Query:
1) Grouping record by using post_id, its foreign key for post_status table.
2) Concatenate status of post using GROUP_CONCAT in descending order of post_status_id.
3) Once each post status has been concatenated, extract the first position status of post from the resultset using SUBSTRING_INDEX.
4) Now the result came, What we expected in SQL Query.
Happy Coding Guys………