What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 122 Stack Exchange communities.

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 meta.stackexchange.com/questions/102638/… . –  Awesome Poodles Dec 18 '11 at 22:16
add comment

3 Answers

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

SELECT Id, Name FROM PostHistoryTypes​

screenshot

screenshot

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):

SELECT
    p.Id AS [Post Link], t.name as [Edit type]
FROM
    Posts p
INNER JOIN 
    PostHistory h ON h.PostId = p.Id
INNER JOIN 
    PostHistoryTypes t ON t.Id = h.PostHistoryTypeId 
WHERE 
    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.

screenshot

share|improve this answer
    
18, not 17. Thanks — this looks promising. Except: data.stackexchange.com/photography/q/122085/… — 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
add comment

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
add comment
up vote 0 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:

http://data.stackexchange.com/photography/query/56730/find-questions-ripe-for-the-revival-and-necromancer-badges

now does what it's supposed to do.

share|improve this answer
add comment

You must log in to answer this question.

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