How can I select the last edited version of a post?

Question

Here is the table structure:

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 1  | content 1                   | NULL      |
| 2  | content 2                   | NULL      |
| 3  | content 1 (edited)          | 1         |
| 4  | content 3                   | NULL      |
| 5  | content 4                   | NULL      |
| 6  | content 4 (edited)          | 5         |
| 7  | content 1 (edited)          | 1         |
+----+-----------------------------+-----------+

Now I want to select the latest edited version of each post. So this is expected result:

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 7  | content 1 (edited)          | 1         |
| 2  | content 2                   | NULL      |
| 4  | content 3                   | NULL      |
| 6  | content 4 (edited)          | 5         |
+----+-----------------------------+-----------+

How can I do that?

Answer 1

SQL Fiddle to see how it works.

First we're removing the " (edited)" string part (where needed) from post_content to preapre a column for group by, then calculating maximum id per our group and finally joining back to the same table to retrieve values for a particular id.

SELECT
  goo.id,
  qa.post_content,
  qa.edited_id
FROM (
  SELECT
    MAX(id) AS id,
    post_content
  FROM (
    SELECT
      id,
      CASE WHEN locate(' (edited)', post_content) <> 0
           THEN left(post_content, locate(' (edited)', post_content) - 1)
           ELSE post_content
           END AS post_content,
      edited_id
    FROM qa
    ) foo
  GROUP BY post_content
  ) goo
  INNER JOIN qa ON goo.id = qa.id

Output

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 7  | content 1 (edited)          | 1         |
| 2  | content 2                   | NULL      |
| 4  | content 3                   | NULL      |
| 6  | content 4 (edited)          | 5         |
+----+-----------------------------+-----------+

CASE explained

-- if post_content contains " (edited)" then locate() will return value of it's position
CASE WHEN locate(' (edited)', post_content) <> 0
  -- we're removing the " (edited)" part by doing left()-1 because we want the string to finish before first character of " (edited)"
     THEN left(post_content, locate(' (edited)', post_content) - 1)
  -- if post_content doesn't contain " (edited)" then simply return post_content
     ELSE post_content
     END AS post_content

Answer 2

Like @PaulSpiegel suggested in the comments, I think a small change in the table structure will make your life much easier.
I suggest you'll change your edited_id with content_id, and give it value on all records, including the original one.

Your data would look like this:

+----+-----------------------------+------------+
| id |       post_content          | content_id |
+----+-----------------------------+------------+
| 1  | content 1                   | 1          |
| 2  | content 2                   | 2          |
| 3  | content 1 (edited)          | 1          |
| 4  | content 3                   | 3          |
| 5  | content 4                   | 4          |
| 6  | content 4 (edited)          | 4          |
| 7  | content 1 (edited)          | 1          |
+----+-----------------------------+------------+

By that, you can use the content_id as an identifier of the content, which save you the need for dealing with null values

The query will look like:

SELECT A.* from myTable as A INNER JOIN (
  SELECT max(id) as id, content_id from myTable group by content_id
) as B ON A.id = B.id 
 AND A.content_id = B.content_id