CODEWITHSUNDEEP
mysql
Asamoa
Asamoa

Reputation: 149

how to get latest value and remove old value?

I'm making a sql command that will get lastest value everytime user ask question or answer question .But there is an error that when i get answer value , it get all answer & question . I want to get only the latest asnwer. Example :

|    postid | type    | parentid | 
|---------  |---------|-----------
| 97        |       A |         92 
| 96        |       A |         93
| 95        |       A |         93
| 94        |       Q |        NULL
| 93        |       Q |        NULL
| 92        |       Q |        NULL
| 91        |       Q |        NULL

I tried to use groupby(parentid) . But it don't get any question . Like this:

|    postid | type    | parentid | 
|---------  |---------|-----------
| 97        |       A |         92
| 96        |       A |         93

But I want to get : the latest answer (don't get answer before of this answer + the question of this answer) the question (if don't have answer , keep this question ) 

|    postid | type    | parentid | 
|---------  |---------|-----------
| 97        |       A |         92
| 96        |       A |         93
| 94        |       Q |        NULL
| 91        |       Q |        NULL

here is my code :

 SELECT `p`.* 
FROM `qa_posts` `p`
JOIN `user_member` `u` ON u.user_id = p.userid
LEFT JOIN `user_profile` `pr` ON u.user_id = pr.user_id
WHERE p.is_deleted =0
GROUP BY `p`.`parent_id`
ORDER BY `p`.`postid` DESC LIMIT 10

Upvotes: 0

Views: 82

Answers (1)

pala_
pala_

Reputation: 9010

How about this?

select max(postid), type, parentid
  from posts
    group by type, parentid

fiddle: http://sqlfiddle.com/#!9/357ce/1

edit
Okay it seems I may have misunderstood, and that you want all unanswered questions, PLUS the latest answer for every other question, but NOT those questions that have an answer? If so, we can do this quite easily with a UNION, this will do it:

select max(postid) p, type, parentid
  from posts
    where type = 'A'
    group by parentid
union
  select p1.postid, p1.type, p1.parentid
    from posts p1
      left join posts p2
        on p1.postid = p2.parentid
      where p2.parentid is null and p1.type = 'Q'
order by p desc;

With an updated fiddle here: http://sqlfiddle.com/#!9/05667/17

Upvotes: 1

Related Questions