2

Please login or register to vote for this query.

(click on this box to dismiss)

Displays all history records for a selected post.

Q&A for professional and enthusiast programmers

WITH PostEdits(GUID, EditDate) AS (
  SELECT
    PH.RevisionGUID,
    (SELECT TOP 1 CreationDate FROM PostHistory AS PH2
     WHERE PH.RevisionGUID = PH2.RevisionGUID
     ORDER BY PH2.Id)
  FROM PostHistory AS PH
  WHERE PostId = ##PostId:int##
    AND PostHistoryTypeId BETWEEN 1 AND 9
), RevisionNumbers(Id, Number) AS (
  SELECT PH.Id, DENSE_RANK() OVER (ORDER BY PE.EditDate)
  FROM PostHistory AS PH
  INNER JOIN PostEdits AS PE
    ON PH.RevisionGUID = PE.GUID
)

SELECT
  PH.RevisionGUID AS [Revision GUID],
  (SELECT TOP 1 RN.Number FROM RevisionNumbers AS RN
   WHERE RN.Id = PH.Id) AS [Revision Number],
  PH.Id AS [Revision ID],
  (SELECT Name FROM PostHistoryTypes AS PHT
   WHERE PHT.Id = PH.PostHistoryTypeId)
    AS [Event Type],
  PH.CreationDate AS [Date],
  PH.UserId AS [User Link],
  PH.Comment,
  [Text]
FROM PostHistory AS PH
WHERE PostId = ##PostId:int##
ORDER BY PH.CreationDate, PH.PostHistoryTypeId

fork query

edited mar 17 14

created dec 22 13 michaelb958