3

It is know that many questions have the same body and title, So I wanted to create a query to show them all, but this don't work :

select
   Posts.id as [Post Link], -- Question title.
   Count(PendingFlags.PostId) as [Number of pending flags], -- Number of pending flags per questions.
   Posts.OwnerUserId as [User Link],
   Reputation as [User Reputation], -- Interesting to see that such questions are sometimes asked by high rep users.
   Posts.Score as [Votes],
   Posts.ViewCount,
   Posts.AnswerCount as [Number of Answers],
   Posts.FavoriteCount as [Number of Stars], -- Some questions seems to be very helpfull :) .
   Posts.CreationDate as [Asked on], -- The older is the question, the more is the chance that flags on them can't get reviewed.
   Posts.LastActivityDate as [last activity], -- Similar effect as with Posts.CreationDate.
   Posts.LastEditDate as [modified on]
from posts 
   LEFT OUTER JOIN Users on Users.id = posts.OwnerUserId
   LEFT OUTER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
where ClosedDate IS NULL -- The question is not closed.
   AND LOWER(body) = LOWER(Posts.title) -- This condition doesn't work : the query doesn't return any results...
group by Posts.id, Posts.OwnerUserId, body, Reputation, Posts.Score, Posts.FavoriteCount, Posts.AnswerCount, Posts.CreationDate, Posts.LastActivityDate, Posts.LastEditDate, Posts.ViewCount
order by Count(PendingFlags.PostId) desc; -- Questions with more flags have more chance to get them handled, and the higher is the probabilty that the question is off-topic (since several users already reviewed the question).
1

As said in the comments to animusons's answer, the rendered version of the body without tags can be found in the PostHistory table. You have to select the most recent entry with a postHistoryTypeId that is either 2 (initial body), 5 (edited body), or 8 (body rollback).

Here is a query that fetches the raw-text body using this approach to find all questions where the body is the same as the title (which returns a table of more than 3000 posts on Stack Overflow):

SELECT p.Id AS [Post Link], p.Body
FROM (
  SELECT * FROM Posts
  WHERE PostTypeId = 1
    AND LEN(Body) < LEN(Title) + 30
    AND LEN(Body) > LEN(Title)
) p
INNER JOIN (
  SELECT ph1.* FROM PostHistory ph1
  INNER JOIN (
    SELECT PostId, MAX(CreationDate) AS CreationDate
    FROM PostHistory
    WHERE PostHistoryTypeId IN (2,5,8)
    GROUP BY PostId
  ) ph2
  ON ph1.PostId = ph2.PostId AND ph1.CreationDate = ph2.CreationDate
  WHERE PostHistoryTypeId IN (2,5,8)
) ph
ON p.Id = ph.PostId
WHERE LOWER(p.Title) = LOWER(ph.Text)
ORDER BY Title
2

That's because the Body contains the rendered version of the text. So even if they copy-paste the Title into the Body, it is at minimum going to be surrounded in a paragraph and end up looking like <p>Body</p>. If there are some other formatting things in the Title that don't get rendered in the Title but do get rendered in the Body, those will also produce some differences.

15
  • Ok thanks. So How I can get the raw text? – user2284570 Jul 28 '14 at 22:06
  • The raw-text version of a post is not available in the Data Explorer. – animuson Jul 28 '14 at 22:07
  • So does it mean my query is impossible to realize? – user2284570 Jul 28 '14 at 22:08
  • Well, you could wrap the Title in paragraph tags when doing the comparison to get some results. But as far as rendering the Title as HTML to get all possible matches, not aware of any way to do that. – animuson Jul 28 '14 at 22:09
  • Mabe with this? – user2284570 Jul 28 '14 at 22:13
  • Stripping the HTML would still only give you results where there is nothing that could be rendered in the Title. If the Title has **something like this**, the asterisks are going to get changed to <b> and </b> respectively in the Body, which would get stripped as HTML and still cause it to not match the Title. – animuson Jul 28 '14 at 22:15
  • You told it yourself, there is no rendering at all in the title. – user2284570 Jul 28 '14 at 22:17
  • @user2284570 Yes, so the asterisks will still be there in the Title. The problem is if the Title is simply copy-pasted, the asterisks will be changed into HTML in the Body. That causes the Title to be different from the Body. If you just strip the HTML from the Body, it will still be different from the Title. – animuson Jul 28 '14 at 22:18
  • 1
    It is available, you just have to get the appropriate PostHistory entry. – Tim Stone Jul 28 '14 at 22:25
  • @TimStone Are all the revisions in the PostHistory table raw-text? – animuson Jul 28 '14 at 22:29
  • Yeah, they're all the source Markdown (or just text in the case of titles) – Tim Stone Jul 28 '14 at 22:30
  • @TimStone : So which is the entry of PostHistorytable I should chose? – user2284570 Jul 28 '14 at 22:32
  • @user2284570 So you could just look for PostHistoryTypeId 2, which is the Initial Body. Then you'd also want to make sure it has no TypeId 5 (Edit Body) which would suggest it's already been edited away from being an exact copy of the Title. – animuson Jul 28 '14 at 22:33
  • @user2284570 The latest one where PostHistoryTypeId IN(2, 5, 8) – Tim Stone Jul 28 '14 at 22:34
  • 1
    @TimStone : May you detail this part in an answer please? (at least the whereclose part) – user2284570 Jul 28 '14 at 22:36

You must log in to answer this question.

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