Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

I'm writing a query to find old questions without much answer attention, to help combat the xkcd "Wisdom of the Ancients" problem. I can filter out closed posts with p.ClosedDate IS NULL, but I'm getting merged questions in the results — a lot of 'em. How can I filter these out too?

share|improve this question
Can you point to some results of your query that are merged questions? (I spot-checked a couple dozen and didn't see any.) If you can, we might be able to determine an indirect way to detect merged posts. – Awesome Poodles Dec 18 '11 at 21:33
@BrockAdams — run it on Photo-SE, and look at the top 3. – mattdm Dec 18 '11 at 21:36
Well, that was a bust. There's nothing in the question record or comments that hints that the Q's are merged. ... Looks like the only recourse is to feature-request that PostHistoryTypes​ value 18 (Question Merged) data be put back in SEDE. – Awesome Poodles Dec 18 '11 at 21:49
Thanks @Brock. Should I do that here, or a new question? – mattdm Dec 18 '11 at 22:00
I'd vote for a new question. Maybe reference this Q and… . – Awesome Poodles Dec 18 '11 at 22:16

I executed the following query, and got the following output:

SELECT Id, Name FROM PostHistoryTypes​



You can search for a question for which one of the history entries (the table is "PostHistory") contains 18 as value of PostHistoryTypeId.

I tried executing the following query on a site with much less questions than Stack Overflow (Drupal Answers):

    p.Id AS [Post Link], as [Edit type]
    Posts p
    PostHistory h ON h.PostId = p.Id
    PostHistoryTypes t ON t.Id = h.PostHistoryTypeId 
    p.ParentID IS NULL AND t.Id > 10​

The only case where I found a "Post Deleted" entry is when the question has been successively undeleted; this would confirm what Jeff Mercado is saying: Entries about deletion, and merging don't appear in SEDE.


share|improve this answer
18, not 17. Thanks — this looks promising. Except:… — there don't appear to be any history entries of that type in the data explorer database. Stripped out? Or am I doing something wrong? – mattdm Dec 18 '11 at 20:24
Whoops… I don't know why, but I read migrated instead of merged. – kiamlaluno Dec 18 '11 at 20:26
Well, it's still on the right track, except for how it doesn't work. :) – mattdm Dec 18 '11 at 20:27
@mattdm: Unfortunately like deletions, it appears merges will not appear in the SEDE. – Jeff Mercado Dec 18 '11 at 20:31
The merged posts are still there, though; you just can't tell if that's why they have no answers. Shame. – mattdm Dec 18 '11 at 20:54

See this "History-type mystery" query and What are these mysterious values for PostHistoryType?

What that shows is that SEDE did once record at least 3661 merged questions, but now that information has been expunged!

From Kevin Montrose:

Most of these weren't meant to be included... They probably won't be included in the next dump.

So, there's no way to (easily) detect merged questions unless you can get that info feature-requested back into SEDE.

share|improve this answer
up vote 1 down vote accepted

I found a workaround — Merged posts are apparently also Locked in the post history, so PostHistoryType 14 works as well as 18. That means this query:

now does what it's supposed to do.

share|improve this answer

You must log in to answer this question.

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