CODEWITHSUNDEEP
sqlstackexchange-api
zeal
zeal

Reputation: 485

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

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.

Upvotes: 4

Views: 378

Answers (1)

double-beep
double-beep

Reputation: 5504

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.

Upvotes: 2

Related Questions