What post has the longest list of revisions on all SE sites? Where is this information available?

  • What did you mean by Never content?
    – Dawny33
    Jun 21 '16 at 5:13
  • 4
    Probably "never satisfied", @dawny - a post that perpetually inspires improvement (or at least change).
    – Shog9
    Jun 21 '16 at 5:24
  • yeah, I like the sound of "never content" but it meant what @Shog9 said
    – nelomad
    Jun 21 '16 at 5:28
  • Please stop adding totally wrong tags, it's considered vandalising the post, no different than changing it to "fsdmfksjkf" Jul 3 '16 at 18:42
  • @ShadowWizard This is fun because it has no real use, and in the answers, it was pointed out that edit is not very precise in describing this.
    – nelomad
    Jul 3 '16 at 18:51
  • 3
    No, it's not "fun". Many feature requests have "no real use", and are just "nice to have" things, plus there are many requests for interesting stats. This does not make them fun. memes are fun. Building castles with <kbd> is fun. Etc. Jul 3 '16 at 18:52

I didn't search other sites yet, but this post on Stack Overflow seems a winner to me. It has a whopping 753 revisions!

You can run this query on SEDE to get the results:

select PostId [Post Link]
,      count(*)
from   posthistory
where  posthistorytypeid in (4,5,6,7,8,9,24)
by     PostId
having count(*) > 100
by     count(*) desc

If you don't want to include rollbacks, you can exclude PostHistoryTypeId 7 to 9 (which are all rollback types). The most edited post seems to be this post on MSE.

  • "edits" is the wrong word, since most are rollbacks (most likely that post also got the longest edit war ever.). Since OP here asked for revisions, not edits, the answer is still correct. :) Jun 21 '16 at 5:45
  • 3
    I have to wonder how an edit war was allowed to continue for more than 700 revisions.
    – David Z
    Jun 21 '16 at 12:42
  • that's so funny :D some people are very passionate about their revisions - may the edits be ever in your favor
    – nelomad
    Jun 21 '16 at 17:21
  • 2
    @DavidZ Most of them happened within a few hours. (That's dedication!) Then the post picked up a couple flags and they were handled by a mod. (I also vaguely recall that we have a "there's probably an edit war" auto-flag, but I remember it exists because I remember fixing it, so I presume it was broken at the time and didn't light the proverbial bat signal when it should have.)
    – Adam Lear StaffMod
    Jun 24 '16 at 5:00
  • I had to edit it because the highlighting formatting wasn't working. Jun 24 '16 at 17:15
  • 1
    Do notice that this query has slightly higher counts on some posts as events are rolled up in one revision, indicated by the same revisionguid. An extreme case is this one
    – rene
    Aug 1 '16 at 8:40
  • Uhm, yes. That is pretty extreme @rene. Aug 1 '16 at 8:43

The answer from Patrick is a reasonable indicator that doesn't hammer the SEDE database to much. It does report higher (sometimes way higher) counts for some posts then the actual /revisions page will show you. This is caused by the fact that some title, body and tag events will be rolled up in one revision, indicated by the same revisionguid, The following query examines all available databases in SEDE and counts the actual revisions per post, including tag wikis:

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , post int
                       , title nvarchar(250)
                       , revcnt int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
select ''' + name + '''
     , postid
     , (select (case when q.title is null and q.parentid is not null then 
                  ( select title 
                   from ' + quotename(name) + '.dbo.posts mq 
                   where mq.id = q.parentid)
               else title
               end) as qtitle
        from ' + quotename(name) + '.dbo.posts q
        where id = postid ) as nqtitle
     , count(*) as cnt
from (select postid
           , revisionguid
      from ' + quotename(name) + '.dbo.posthistory ph 
      where ph.posthistorytypeid < 10 -- all edits
      group by postid
             , revisionguid
      ) as data
group by postid
having count(*) > 10
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select top 10000
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      + '/posts/'
      + cast(post as nvarchar) 
      + '/revisions'
      + '|'
      + case when title is null 
             then 'tag wiki:' + cast(post as nvarchar) 
             else title
        end as [Post]
      -- rest of columns
      , revcnt as [Number of revisions]
from #results
order by revcnt desc

drop table #results

When run today the result looks like this:

posts per site with revision count

For a per database query you can use this version

  • Sorry, but I'm awfully uneducated in this (and probably need more sleep), but how is this more accurate than Patrick's answer?
    – nelomad
    Aug 2 '16 at 21:26
  • 1
    @Adamawesome4 you are asking for the most changed post, aka revisions. That is the number shown on the post revision page. If you look at this one, it shows 308 revisions but if you take that same post and look at the posthistory events you'll find 924 rows. Patrick his query will report the 924, mine reports the 308. I role-up multiple revisonguids. So that is why I say mine is (functional) more accurate because it matches the changes users can check on any post they see.
    – rene
    Aug 3 '16 at 7:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .