-1

This is my table structure:

// posts
+----+-----------------------------+-----------+------------+
| id |            body             | edited_id | date_time  |
+----+-----------------------------+-----------+------------+
| 1  | post1                       | NULL      | 1464136759 |
| 2  | post2                       | NULL      | 1464136760 | 
| 3  | post2     edited            | 2         | 1464136761 | 
| 4  | post2     eduted again      | 2         | 1464136762 | 
+----+-----------------------------+-----------+------------+

As you can see, table above keeps both posts and their edition version. Currently I'm selecting posts like this:

SELECT * FROM posts WHERE id IN (1,2) ORDER BY date_time ASC;

Current output:

| 1  | a question                  | NULL      | 1464136759 |
| 2  | my answer                   | NULL      | 1464136760 |

Expected output:

| 1  | a question                  | NULL      | 1464136759 |
| 4  | my answer eduted again      | 2         | 1464136762 |

see? The post id = 2 has two edition versions, and I always need to select be biggest one (according to date_time). How can I do that?

Martin AJ
  • 6,261
  • 8
  • 53
  • 111
  • 1
    how you know that id2 is answer to question in id1? – Pawel Szalucki May 14 '18 at 13:09
  • The problem is when "4" is edited. You have a graph problem and MySQL has basically no support for hierarchical or recursive queries. – Gordon Linoff May 14 '18 at 13:09
  • @GordonLinoff According to my current policies, edited rows won't be edit again. All edition versions point to the original post. – Martin AJ May 14 '18 at 13:14
  • @GordonLinoff well, v8.0 is now in production status, so this is not strictly true any longer (see https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive). Obviously, this requires an upgrade to v8.0 – Shadow May 14 '18 at 13:18
  • @MartinAJ in this case it is the traditional get record with maximum value within a group question, which has already been asked and answered here on SO. – Shadow May 14 '18 at 13:22
  • @Shadow Can you please rephrase "... get record with maximum value within a group question ..." – Martin AJ May 14 '18 at 13:24
  • @MartinAJ I gave you a link to a duplicate topic, which not only contains various solutions, but the most upvoted ones also provide explanations. This way you can learn how to do this yourself. – Shadow May 14 '18 at 13:26

1 Answers1

0

If the rows won't be edited, then you can use:

select p.*
from posts p
where (p.id in (1, 2) or p.edited_id in (1, 2)) and
      p.id = (select p2.id
              from posts p2
              where p.id in (p2.id, p2.edited_id)
              order by date_time desc
              limit 1
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786