2

I want to find the top K viewed questions with its related answerers and askers, but when I run the following query:

select
    question.Id as QuestionId,
    question.Score as QScore,
    question.ViewCount as QViewCount,
    question.OwnerUserId as Asker,
    question.Title,
    question.Tags,
    question.AnswerCount as QAnswerCount,
    answer.Id as AnswerId,
    answer.ParentId,
    answer.OwnerUserId as Answerer
from
    Posts as question inner join
    Posts as answer on
    question.Id = answer.parentId
where
    question.Id in (
        select top 2000 Id
        from Posts
        where PostTypeId=1
        order by ViewCount desc
      )

It turned out to be

"Something unexpected went wrong while running your query. Don't worry, blame is already being assigned".

I don't know why this happened. I need help.

  • top 2000 is SQL-Server Syntax, not mysql – Jens May 26 '17 at 9:30
  • limit 2000 i think you want – jimmy8ball May 26 '17 at 9:31
  • this will only test the question id against the first 2000 records from posts, is this what you need? – jimmy8ball May 26 '17 at 9:32
  • yes I want the top 2000 viewed questions – joyceT May 26 '17 at 9:35
  • Did you try the query again? That's usually a transient error. – Jason C May 26 '17 at 11:16
2

This particular error message has a number of unknown causes and is usually transient. It is different than "query processor ran out of resources" or "timed out". The general solution is to try it again a few seconds or minutes later. It's usually not a sign that your query is too complex.

Your query works fine, as is, and executes in 2-4 seconds (change the number in that last SELECT 1, or one of your column names, to break the server-side cache and force a re-run for testing). Even without limiting to the top 2000 questions it runs fine in about 30-40 seconds on SO.

-1

OK, limit your results like below:

select
    question.*,
    answer.Id as AnswerId,
    answer.ParentId,
    answer.OwnerUserId as Answereranswer
from
(select
    question.Id as QuestionId,
    question.Score as QScore,
    question.ViewCount as QViewCount,
    question.OwnerUserId as Asker,
    question.Title,
    question.Tags,
    question.AnswerCount as QAnswerCount
 from Posts as question
where
    question.Id in (
        select Id
        from Posts
        where PostTypeId = 1
        order by ViewCount desc
        limit 2000
      )
)question
inner join
Posts as answer on question.Id = answer.parentId
  • thanks,but it's Incorrect syntax near 'limit'. – joyceT May 26 '17 at 11:02
  • As an aside: Changes like this do not make a difference. The query optimizer will select only the necessary columns anyways. You can see this, for example, if you view the execution plan on sede then mouse over the boxes and check the output lists. Also, question.* in the result set would make this a bit worse, significantly worse in bandwidth usage sending you the response, as that causes all the columns to be selected and returned, including things like Posts.Body which weren't desired in the OP's original query. – Jason C May 26 '17 at 11:30

You must log in to answer this question.

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