-6

Out of all the negatively voted questions on SE, which one has the most answers (at the time of this post)?

  • Negatively voted: does that mean a score < 0? Or ever received a down vote? – rene Jan 12 '18 at 18:10
  • @rene negative score. For example see this question (although it has 0 answers, and so wouldn't be the answer to this question). – PyRulez Jan 12 '18 at 18:13
6

I believe this query to do that:

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , title nvarchar(250)
                       , id int
                       , score int
                       , answercount int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , title
     , id
     , score
     , answercount
from ' + quotename(name) + '.dbo.posts 
where score < 0 
and answercount > 0
and posttypeid = 1
'
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select
      site
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      , 'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + '/q/'
      + cast(id as nvarchar(20)) 
      + '|'
      + title as [Link to Question]
      , score 
      -- rest of columns
      , answercount
from #results
order by answercount desc

drop table #results

Here is a screenshot of its top results when run today:

enter image description here

The per site query is pretty straight forward:

select title
     , id
     , score
     , answercount
from posts 
where score < 0  -- score less then 0
and answercount > 0 -- more then 0 answers
and posttypeid = 1 -- questions only 

You must log in to answer this question.

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