declare @now datetime=getutcdate(); with ordered as( sel...

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 @now datetime=getutcdate();

with
ordered as(
	select ParentId,
		ROW_NUMBER() over (partition by ParentId order by CreationDate desc) RDate,
		ROW_NUMBER() over (partition by ParentId order by CreationDate asc) RDateReverse,
		ROW_NUMBER() over (partition by ParentId order by Score asc, Id asc) RCustom
	from Posts where PostTypeId=2
),
compared as(
	select ParentId,
		case when count(case when RDate!=RCustom then 1 else null end)=0 then 1 else 0 end as ok,
		case when count(case when RDateReverse!=RCustom then 1 else null end)=0 then 1 else 0 end as okreverse

	from ordered
	group by ParentId
)

select count(*) from compared where
--ok=1 -- 44945
--ok=1 and (okreverse = 0 or okreverse = 1) -- 42724
--ok=1 and (okreverse = 0 or okreverse = 1 or okreverse != 0 or okreverse != 1 or okreverse is null)
ok = 1-- and (okreverse=okreverse or okreverse is null)

Enter Parameters

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