SELECT pq.Id as question_id, pq.AcceptedAnswerId as accep...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity

Q&A for committed Christians, experts in Christianity and those interested in learning more

SELECT
  pq.Id as question_id,
  pq.AcceptedAnswerId as accepted_answer_id,
  pa.Id as max_score_answer_id,
  pa.score as max_score,
  pa2.Score as accepted_score
FROM posts pq

LEFT JOIN 
  (
    SELECT * 
    FROM posts pa 
    WHERE pa.ParentId IS NOT NULL
  ) pa2 on pa2.Id = pq.AcceptedAnswerId

LEFT JOIN 
  (
    SELECT * 
    FROM posts pa 
    WHERE pa.ParentId IS NOT NULL
  ) pa on pa.ParentId = pq.Id

INNER JOIN
    (
      SELECT MAX(p2.Id) as Id, p2.ParentId, MAX(p2.Score) AS max_score
      FROM posts p2
      WHERE p2.ParentId IS NOT NULL
      GROUP BY p2.ParentId
      ) p_max on ISNULL(pq.Id, 0) = ISNULL(p_max.ParentId, 0) AND ISNULL(pa.score, 0) = ISNULL(p_max.max_score,0)


WHERE pq.ParentId = 39231
ORDER BY question_id

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results