CODEWITHSUNDEEP
mysqlsql
user1043994
user1043994

Reputation:

Get latest row for threads in QA table

This a simplified version of my SQL table for questions and answers, post_id is the primary key.

 post_id | ref_post_id | title  | date (unix timestamp)
   1     |     0       | Title1 |  10 
   2     |     1       |        |  20 
   3     |     0       | Title2 |  30

When ref_post_id is 0, that means it's a question. When it is not 0 it's an answer where the number corresponds to the question's post_id. That means that the second row is an answer to the first row in my example above.

What I want to do is to get only the last row of every question. If the question does not have any answers I want the question row, but if it has answers I want the last answer but I want to join in the title that belongs to the question row.

I've googled and tried myself for a while now. Hopefully you understand my problem!

Thanks!

Upvotes: 0

Views: 111

Answers (5)

fthiella
fthiella

Reputation: 49079

I already posted a solution, but assuming post_id is always ordered, i like this one more since it's more simple:

select
  posts.*, posts_1.title
from
  posts left join posts posts_1
  on posts.ref_post_id = posts_1.post_id
where posts.post_id IN (select max(post_id) from posts
group by IF(posts.ref_post_id=0,posts.post_id,posts.ref_post_id))

(i'm using the same trick of grouping by ref_post_id or by post_id).

EDIT: if you need to select only undeleted posts, this is what you need:

select
  posts.*,
  posts_1.title
from
  posts left join posts posts_1
  on posts.ref_post_id = posts_1.post_id
where
  posts.post_id IN (select max(post_id) from posts
                    where exists (select * 
                      from posts posts_sub
                      where
                        posts_sub.post_id =
                        if(posts.ref_post_id=0,posts.post_id,posts.ref_post_id)
                        and posts_sub.deleted =0)
group by
  IF(posts.ref_post_id=0,posts.post_id,posts.ref_post_id))

Upvotes: 0

fthiella
fthiella

Reputation: 49079

I would reccommend to split your table in two different tables, one for the questions and one for the answer.

However, the query you need could be written as this:

SELECT posts.*
FROM
  posts INNER JOIN
    (SELECT IF(ref_post_id=0,post_id,ref_post_id) as id, Max(Date) as maxdate
     FROM posts
     GROUP BY id) p
  ON
    (p.maxdate = posts.date) AND
    (p.id =IF(posts.ref_post_id=0,posts.post_id,posts.ref_post_id))

To select max date for every question/answer you have to group by ref_post_id, except the case in which ref_post_id = 0 where you have to group by post_id, hence the IF.

When you join the posts table with itself, using the subquery, you have to use the same trick.

EDIT: if you need to mark a question as deleted, then you have to remove all deleted questions and answers from the select above, and you have to use one more subquery to select only undeleted questions:

SELECT posts.*
FROM
  posts INNER JOIN
    (SELECT IF(ref_post_id=0,post_id,ref_post_id) as id, Max(Date) as maxdate
     FROM posts
     WHERE EXISTS (SELECT null 
                   FROM posts posts_sub
                   WHERE
                     posts_sub.post_id =
                     IF(posts.ref_post_id=0,posts.post_id,posts.ref_post_id)
                   AND posts_sub.deleted =0)
     GROUP BY id) p
  ON
    (p.maxdate = posts.date) AND
    (p.id =IF(posts.ref_post_id=0,posts.post_id,posts.ref_post_id))

Upvotes: 0

Guoliang
Guoliang

Reputation: 895

How about this one:

SELECT t.*, lastAnswer.title  FROM tableName t LEFT JION 
(SELECT t1.*
    FROM tableName t1 LEFT JOIN tableName t2
     ON (t1.ref_post_id = t2.ref_post_id AND t1.date < t2.date)
    WHERE t2.ref_post_id IS NULL AND t1.ref_post_id > 0
) lastAnswer
ON t.post_id = lastAnswer.ref_post_id
WHERE t.ref_post_id = 0

Upvotes: 0

svenbravo7
svenbravo7

Reputation: 317

I would recommend to split your table in two, one for the questions and one for the answers.

A rough sketch of how this would look:

questions
post_id  |  title  |  date

answers
answer_id  |  post_id  |  answer  |  date

That way you can request the last answer of the requested question in the following way:

SELECT * FROM answers WHERE post_id = THE_REQUESTED_POST_ID ORDER BY date DESC LIMIT 1

EDIT:

Crap sorry, I said the wrong table, the table to select from should be answers.

Upvotes: 2

John Woo
John Woo

Reputation: 263803

SELECT  a.*, b.replyID
FROM    tableName a
        LEFT JOIN tableName c
            ON a.ref_post_ID = c.post_id
        LEFT JOIN
        (
            SELECT ref_post_ID replyID, MAX(DATE) as maxDATE
            FROM tableName
            GROUP BY replyID
        ) latestAnswer
            ON  c.ref_post_ID = latestAnswer.replyID AND
                c.date = latestAnswer.maxDATE

I highly recommend, that you make a separate table for this, one is for Question and second for Answers and which a column from Answers table is a foreign from Question table.

Upvotes: 1

Related Questions