How to extract the accepted and the top voted answer of a question from SEDE?

Question

I am writing a query to extract questions and answers from Stack Overflow Stack Exchange Data Explorer using the following query:

SELECT Top 2 
q.id, q.Title, q.Body q_body, q.parentid qpid, q.AcceptedAnswerId, q.Score q_score, q.AnswerCount, q.Tags, q.viewcount,

answers.parentid aa_pid, answers.id aa_id,
answers.body aa_body, answers.score as aa_score, answers.posttypeid 

FROM 
  Posts answers

INNER JOIN Posts q 
  ON answers.parentid = q.id

WHERE (answers.id = q.AcceptedAnswerId) AND q.AnswerCount > 2

But, along with accepted answer, I also want to extract the most voted answer. Since, I can not even understand why answers is not a table.

Please check https://data.stackexchange.com/stackoverflow/query/1145387/extract-accepted-and-most-voted-answer-of-a-question. It doesn't extract the question and it extracts only the accepted answer if it is the most voted one. Does that work for you? — double-beep, Nov 10 '19 at 13:30

score 1 · Answer 1 · answered Dec 16 '20 at 11:12

Here's a query I came up with and you can see it live:

SELECT Id AS [Post Link], Body, Score
FROM (
    SELECT TOP 1 Id, Body, Score, DENSE_RANK() OVER(ORDER BY Score DESC) Rank 
    FROM (
        SELECT p.Id, p.ParentId, p.Body, p.Score
        FROM Posts p 
        WHERE p.ParentId = ##QuestionId##
    )x
    ORDER BY Rank ASC
)x

UNION
SELECT * 
FROM (
    SELECT Id, Body, Score
    FROM (
        SELECT a.Id, a.ParentId, a.Body, a.Score FROM Posts a 
        WHERE a.ParentId = ##QuestionId##
    )x
)x 
WHERE (SELECT b.AcceptedAnswerId FROM Posts b WHERE b.id = ##QuestionId##) = id

The first piece finds the top voted answer, while the second one searches for the accepted answer (if there's one).

Both are a bit complicated because Posts table is about answers as well as questions. This is the reason for the two nested FROMs.

How to extract the accepted and the top voted answer of a question from SEDE?

1 Answers1