declare @revisionGuid as uniqueidentifier = '1073f9bc-dba...

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

declare @revisionGuid as uniqueidentifier = '1073f9bc-dbaf-47e0-97f0-065b968fbd3b';
declare @postId as int = 40356855;

with RevisionNumbers as
(
    select   RevisionGUID, 
        ROW_NUMBER() over (
                order by min(CreationDate),
                         min(Id)
             )  RevNum
    from     PostHistory
    where    PostId = @postId
    group by RevisionGUID
),

RelevantRevisionNumbers as
(
    select RevisionGUID, RevNum
    from RevisionNumbers
    where RevNum <= (select RevNum from RevisionNumbers where RevisionGUID=@revisionGuid)
)

select *
from
(
    select top 1 h.*
    from PostHistory h
    join RelevantRevisionNumbers rn on rn.RevisionGUID=h.RevisionGUID
    where PostHistoryTypeId in (1,4,7)
        and PostId = @postId
    order by RevNum desc

    union all

    select top 1 h.*
    from PostHistory h
    join RelevantRevisionNumbers rn on rn.RevisionGUID=h.RevisionGUID
    where PostHistoryTypeId in (2,5,8)
        and PostId = @postId
    order by RevNum desc

    union all

    select top 1 h.*
    from PostHistory h
    join RelevantRevisionNumbers rn on rn.RevisionGUID=h.RevisionGUID
    where PostHistoryTypeId in (3,6,9)
        and PostId = @postId
    order by RevNum desc
) x

Enter Parameters

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