2

Please login or register to vote for this query.

(click on this box to dismiss)

Threads affected by the unpinning of the accepted answer (as described in https://meta.stackexchange.com/questions/369568/unpinning-the-accepted-answer-from-the-top-of-the-list-of-answers), i.e., threads with answers that score higher than the accepted answer, where the accepted answer was not written by the OP.

Q&A for professional and enthusiast programmers

select distinct 
q.Id as [Post Link], 
q.CreationDate as Created,
case 
  when q.ClosedDate is null then ''
  else (case when pl.LinkTypeId = 3 then 'dupe' else 'closed' end)
  end as 'Status',
aa.Score as 'Accepted score', 
hs.Score as 'Highest score',
hs.Score - aa.Score as 'Score difference'
from 
Posts as q 
left join Posts as aa on q.Id = aa.ParentId 
left join Posts as hs on q.Id = hs.ParentId
left join PostLinks as pl on q.Id = pl.PostId
where 
q.PostTypeId = 1 and
q.AcceptedAnswerId is not null and
q.AnswerCount > 1 and
aa.Score < hs.Score and
aa.Id = q.AcceptedAnswerId and
hs.Score = (Select max(score) from Posts csq where csq.ParentId = q.id) and
(aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null) and
((pl.LinkTypeId = (Select max(LinkTypeId) from PostLinks cpl where cpl.PostId = q.id))or pl.LinkTypeId is null)
--order by hs.Score - aa.Score DESC;
order by Created DESC;

fork query

edited sep 10 21 E.P.

created sep 10 21 Nihar.Karve