11

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?

11

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:

results from comment score across the network

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.

| improve this answer | |
  • 1
    When audio then video :D – Ver Nick Nov 24 '19 at 16:02
  • I love that you convert the site names. 💚 – Catija Nov 24 '19 at 16:20
4

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.

enter image description here

| improve this answer | |
  • sure he can change /stackoverflow/ to something else, it was just example – genesis Jul 11 '11 at 16:31
  • @genesis yeah I get that but the where clause on the query was WHERE Score> 300​ this returns 0 results for EL&U – Some Helpful Commenter Jul 11 '11 at 17:11
  • I even don't know what EL exactly is :D So if I would know that, I would edit it for his needs – genesis Jul 11 '11 at 17:12
  • @Genesis based on drm65's account profile I'm guessing English Language and Usage – Some Helpful Commenter Jul 11 '11 at 17:38
  • It doesn't change the current top comment for EL&U (thanks Karl Stefanovic) but for the main sites Top non-community-owned Comment highlights some other interesting comments. – Mark Hurd Jun 1 '12 at 16:48
3

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

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

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

| improve this answer | |
  • 6
    Both links above are dead – Mu Mind Oct 5 '12 at 15:44
2

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)
| improve this answer | |

You must log in to answer this question.

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