with rev(pid, id, bid) as ( select top 1000 ph.postid pid...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with rev(pid, id, bid) as
(
select top 1000
       ph.postid pid
     , ph.id id
     , -1 bid
from posthistory ph
where ph.posthistorytypeid = 2 -- initial body
union all
select phr.postid
     , phr.id
     , fr.id
from posthistory phr
inner join rev fr on fr.pid = phr.postid
where phr.posthistorytypeid in (5,8) -- revision, rollback       
and phr.id > fr.id
)
, revall as (
select pid as postid
     , bid as baseid
     , min(id) as nextid
from rev
group by pid, bid

)

select revall.postid as [Post Link]
, len(phnext.text) - len(phbase.text) [diff]
from revall
inner join posthistory phbase on revall.baseid = phbase.id
inner join posthistory phnext on revall.nextid = phnext.id
where baseid <> -1
order by revall.postid, baseid

select *
from posthistory 
where postid = 52238064

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results