Mysql – How to select the last edited version of the post

greatest-n-per-groupMySQLselect

I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1  | title1  | question content          | NULL    | 0    | NULL      |
| 2  |         | answer content            | 1       | 1    | NULL      | 
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
| 5  |         | answer content            | 1       | 1    | NULL      |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 7  | title3  | question content          | NULL    | 0    | NULL      |
| 8  | title1  | question content (edited) | NULL    | 0    | 1         |
| 9  |         | answer content            | 7       | 1    | NULL      |
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
+----+---------+---------------------------+---------+------+-----------+

Column explanations:

related column:

  • NULL for both questions and edited version of questions/answers
  • {the id of its own question} for answers

type column:

  • 0 for questions
  • 1 for answers

edited_id column: (the id of original post)

  • NULL means it is a original question/answer
  • {any number} means it is a edited version of a question/answer.

Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).

Example1: I have this value: :id = 1 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 5  |         | answer content            | 1       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 3 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 7 and I want this output:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
| 9  |         | answer content            | 7       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Here is my current query:

SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
      (related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter

As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?

Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.

Best Answer

You need a groupwise max. I think you will need one query to find the "latest" question, then one for the latest answer. Then UNION ALL the results together in order to get them in the same resultset.