MySQL self join to find only the next revision

Actually I’m working with WordPress. I want to create a self-join or something similar to find a revision of a post, and the following revision of the same post.

create table wp_posts (post_id int, revision_id int);
INSERT INTO wp_posts(post_id, revision_id) VALUES (1, 1);
INSERT INTO wp_posts(post_id, revision_id) VALUES (1, 2);
INSERT INTO wp_posts(post_id, revision_id) VALUES (1, 3);

INSERT INTO wp_posts(post_id, revision_id) VALUES (2, 11);
INSERT INTO wp_posts(post_id, revision_id) VALUES (2, 12);
INSERT INTO wp_posts(post_id, revision_id) VALUES (2, 13);

SELECT a.post_id, a.revision_id "PreviousRevision", b.revision_id "FollowingRevision"
  FROM `wp_posts` a
  JOIN `wp_posts` b
    ON a.post_id = b.post_id   #the id of every revision of a post is different but the post_id is the same
 WHERE a.revision_id < b.revision_id
 AND a.revision_id != b.revision_id

https://www.db-fiddle.com/f/eHnwYABYrVVQAhn8xLJ77q/1

The previous query doesn’t work since it takes, for every record of a, all the revision which have been made and not just the next one.

This is what I get, and I’ve striked out the lines I don’t want. I need only father-child rows.

How can I take just one element?

Answer

For this sample data you need the maximum a.revision_id for each combination of a.post_id, b.revision_id:

SELECT 
  a.post_id, 
  MAX(a.revision_id) "PreviousRevision", 
  b.revision_id "FollowingRevision"
FROM `wp_posts` a JOIN `wp_posts` b
ON a.post_id = b.post_id   
WHERE a.revision_id < b.revision_id
GROUP BY a.post_id, b.revision_id

Also the condition a.revision_id != b.revision_id is not necessary beacause you already have a.revision_id < b.revision_id.
See the demo.
Results:

| post_id | PreviousRevision | FollowingRevision |
| ------- | ---------------- | ----------------- |
| 1       | 1                | 2                 |
| 1       | 2                | 3                 |
| 2       | 11               | 12                |
| 2       | 12               | 13                |