Is there a query that will list the highest voted post (question or answer) for each tag on a site?


I think this SEDE query I just wrote does the job.

enter image description here

For reference, here is the complete query:

SELECT TagName, QuestionID AS [Post Link], q_.Score AS [Q score],
  AnswerID AS [Post Link], a_.Score AS [A score]
(SELECT TagName,
  (SELECT TOP 1 q.Id
     FROM Posts AS q INNER JOIN PostTags AS qt ON q.Id = qt.PostId
     WHERE qt.TagId = t.Id ORDER BY q.Score DESC) AS QuestionID,
  (SELECT TOP 1 a.Id
     FROM Posts AS a INNER JOIN PostTags AS at ON a.ParentId = at.PostId
     WHERE at.TagId = t.Id ORDER BY a.Score DESC) AS AnswerID
  FROM Tags AS t) AS temp
  INNER JOIN Posts AS q_ ON temp.QuestionId = q_.Id
  LEFT OUTER JOIN Posts AS a_ ON temp.AnswerId = a_.Id
  ORDER BY q_.Score DESC

and note that SEDE is updated only once a week, on Sunday morning.

| improve this answer | |
  • Is there anyway to make some rows multi-line, so titles don't get cutoff? – PyRulez Jan 23 '18 at 6:54
  • Also, an interesting variation would be to make it so restrict it to certain tags. What I mean by that is restricting the query to only posts with the tag tags, for example. – PyRulez Jan 23 '18 at 7:02
  • @PyRulez no, but you can export to CSV, open with Excel and do anything you want with the data. – Shadow Wizard Wearing Mask Jan 23 '18 at 7:22
  • @ShadowtheHedgehogWizard The CSV doesn't have the post titles at all. – PyRulez Jan 23 '18 at 7:24
  • @PyRulez oh, didn't know. – Shadow Wizard Wearing Mask Jan 23 '18 at 7:39
  • @PyRulez multiline isn't possible, but you could resize the columns. You can also fork the query to create your own versions, e.g. this one which shows only Java / JavaScript tags on Stack Overflow. – Glorfindel Jan 23 '18 at 8:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .