Edits of the given length

1

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

; with nextedit_cte as
(SELECT
  ph1.Id AS phid,
  MIN(ph2.Id) AS nextid
FROM PostHistory ph1 INNER JOIN PostHistory ph2 ON ph1.PostId=ph2.PostId
WHERE (ph1.PostHistoryTypeId IN (2,5,8)) AND (ph2.PostHistoryTypeId=5) AND (ph1.Id<ph2.Id)
---2 = Initial Body - initial post raw body text
---5 = Edit Body - modified post body (raw markdown)
---8 = Rollback Body - reverted body (raw markdown)
GROUP BY ph1.Id
), lengths_cte AS (SELECT
  phid, nextid,
  LEN(ph1.Text) AS oldlength,
  LEN(ph2.Text) AS newlength,
  CAST((LEN(ph2.Text)-LEN(ph1.Text)) AS int) diflength
FROM PostHistory ph1 INNER JOIN nextedit_cte ne ON ph1.Id=ne.phid
  INNER JOIN PostHistory ph2 ON ph2.Id=ne.nextid
)

SELECT TOP(##num?500##)
  ph.PostId AS [Post Link],
  concat('site://posts/',
          ph.PostId,
          '/revisions'
          ) [Revisions Link],
  diflength, oldlength, newlength,
  ph.Comment,
  ph.UserId AS [User Link],
  ph.CreationDate
FROM PostHistory ph INNER JOIN lengths_cte l ON ph.Id=l.phid
WHERE (diflength=##dif?0##)
ORDER BY ph.CreationDate DESC

Enter Parameters

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