Is there a way to find the highest voted comment on a particular SE site?

Question

On EL&U, there is a 123-vote comment, and I'm wondering if it's the highest or not. How can I find out?

Answer 1

The other answers are busy offering per-site solutions so I thought I could offer a networkwide SEDE query.

The query takes the top 1000 comments from each site and then presents the top 1000 of those. This top 1000 is needed to limit the time it takes to process all the records.

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , displayname nvarchar(150)
                    , userid integer
                    , commentid integer
                    , comment nvarchar(600)
                    , score integer
                    , creationdate datetime)

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select site
     , hostname
     , displayname
     , uid
     , cid
     , text
     , score
     , creationdate
from (
select top 1000 ''', name ,N''' as site
     , ''',hostname ,N''' hostname
     , u.displayname
     , u.id uid
     , c.id cid
     , c.text
     , c.score
     , c.creationdate
from ', quotename(name), N'.dbo.comments c
left outer join ', quotename(name), N'.dbo.users u on
  c.userid = u.id
order by score desc
) data
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
-- (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)


-- https://meta.stackexchange.com/questions/332237/documentation-for-stack-exchange-engine-urls
-- comment link idea fron Glorfindel
select top 1000 
       site
     , concat('https://'
       , hostname
       , '/posts/comments/'
       , commentid
       , '|'
       , comment
       ) [Link to Comment]
     , concat('https://'
       , hostname
       , '/users/'
       , userid
       , '|'
       , coalesce(displayname, '<?>')
       ) [Owning user]
     , creationdate
     , score
from #result
order by score desc

When run today the result is this:

Keep in mind SEDE is updated once a week, on Sunday morning.
Give a big shout out to Monica Cellio for her awesome SEDE tutorial.
Say "Hi!" in SEDE Chat if you have any inquiries.

Answer 2

Of course if you want the top EL&U score the query genesis linked to won't help because it only shows comments Greater than 300. Which not every site has.

This query Top Comment score just finds the top.

Because the data gets refreshed periodically The top comment on 6/6/2011 was on How do you quote a passage that has used '[sic]' mistakenly? so the 123 vote may be the current top.

As an aside if you ever want to change which SE site a query is run against you can just click the image next to the Run Query button for the site. Note it only appears after you run the query.

Answer 3

There is no normal way how can you retreive most-voted comment, however you can compose a query

https://data.stackexchange.com/stackoverflow/qt/105272/

https://stackoverflow.com/questions/2349378/new-programming-jargon-you-coined/2353436#2353436

Answer 4

https://data.stackexchange.com/meta.stackexchange/query/1158372/top-comments-sitewide

Alternatively, to find your top comments:

https://data.stackexchange.com/meta.stackexchange/query/1158370/top-comments

• You have to manually enter the userid in the box at this page
• Edit the URL to change the site it is for (don't include.com)