6

I want to write a query which selects the highest scored community-wiki-lock questions (like this one). This is not typical comunity-wiki question like this - but comunity-wiki-lock (e.g. we see there the original question author without 'wiki' info). For community-wiki it is enough to use CommunityOwnedDate is not null in query, but it not working for wiki-locked questions. This is what I have so far:

https://data.stackexchange.com/stackoverflow/query/1166505/find-top-community-wiki-locked-questions

  1. How to do it?
  2. How to select non community-wiki-lock questions?

1 Answer 1

5

Those are post notices with a specific type ID, namely 23. You can find them by joining to the PostNotices table:

INNER JOIN PostNotices AS pn
   ON p.Id = pn.PostId
  AND pn.PostNoticeTypeId = 23
  AND pn.DeletionDate IS NULL -- non-deleted notices only

This query helps to find them; there are currently only 48 of those on Stack Overflow.

enter image description here

To exclude these kind of questions, you could use an outer join:

LEFT OUTER JOIN PostNotices AS pn
   ON p.Id = pn.PostId
  AND pn.PostNoticeTypeId = 23
  AND pn.DeletionDate IS NULL

and add AND pn.Id IS NULL to your WHERE clause.

You might wonder where that magic number 23 is coming from. There's a small table PostNoticeTypes, if you scroll down in the sidebar widget with the database schema, you'll notice it has a little 'information' icon (left). When you click on it, you see the contents of the table (right). Scroll down until you reach the post notice type you're looking for:

enter image description here enter image description here

0

You must log in to answer this question.

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