4

How to find the list of most upvoted questions/answers on a stackexchange site other than Stack Overflow? For example, music.stackexchange.com or math.stackexchange.com etc.

All the answers to What are the most upvoted/downvoted questions and answers on the sites? are about Stack Overflow only, so don't provide me with this link.

Note: I have no idea what a SEDE query is.

  • You need to use SEDE queries per SE site to find that out. Each site keeps its own database. – πάντα ῥεῖ Jan 19 at 14:45
  • It is a query done with the SE Data Explorer as e.g. referenced here. – πάντα ῥεῖ Jan 19 at 14:49
  • If you put /questions?sort=votes after the site url you should get the answer to half your question (eg travel.stackexchange.com/questions?sort=votes) but I have no idea how to reverse the list so this is just a comment not an answer. – mdewey Jan 19 at 15:07
4

Use the search option is:a (for answers) or is:q (for questions) and sort on 'votes':

https://music.stackexchange.com/search?q=is%3Aa&tab=votes

Example screenshot of the above query

You can also use is:answer or is:question for clarity.

4

In addition to what Glorfindel said:

As commenters noted you can use SEDE ("Stack Exchange Data Explorer") to find such posts. SEDE is an open-source project that allows you to execute SQL-queries against copies of Stack Exchange's databases.

If you search for "most upvoted" you find these two queries:

The user who posted these queries has also two opposite queries for the most downvoted posts:

Currently all these queries are running against Stack Overflow's database. Below the code box there is a small site switcher though:

Site switcher

You can click on the gray site icon to toggle between the main and the meta site. If you enter the name or url of a site in the text box you will see a list of matching sites. Upon selecting one you will see that the page reloads and the query will be executed against some other sites database.

Note: You will probably need to solve a captcha before executing the queries. If you want to avoid this you can login/register an SEDE account. Also note that SEDE will only be updated once a week so the results might be a bit out of date.

1

This query shows you the top posts with the most up votes per site across the network, excluding per site metas and Stack Overflow:

declare @sql nvarchar(max)

create table #result( site sysname
                    , postid integer
                    , posttypeid integer
                    , title nvarchar(250)
                    , votetypeid integer
                    , votes integer);

select @sql = concat('insert into #result', string_agg(concat(N'
select * from (select top 1
 ''' , convert(nvarchar(max),name), N''' site 
     , v.postid 
     , p.posttypeid
     , q.title
     , v.votetypeid
     , count(*) [#votes]
from ',convert(nvarchar(max),quotename(name)), N'.dbo.votes v
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts p on p.id = v.postid
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts q on q.id = nullif(p.parentid, p.id)
where p.posttypeid in (1,2) -- q, a
and v.votetypeid = 2 -- up
group by v.postid, q.title, p.posttypeid, v.votetypeid
order by count(*) desc
) data
'), 'union all'))
from sys.databases
where database_id > 5
and (name not like '%.meta' or name = 'stackexchange.meta')
and not (name = 'stackoverflow')

print @sql

exec (@sql)

select 
  site
  , concat('https://'
   , case 
     when len(parsename(site,2)) > 0 then
       concat(parsename(site,1),'.',parsename(site,2))
     else
       site
     end
    , '.com'
    , '/q/'
    , postid
    , '|'
    , case posttypeid when 1 then '[Q] ' else '[A] on ' end
    , title
    ) [Post Link]
  , case votetypeid when 2 then 'Up' else 'Down' end [Up/Down]
  , votes
from #result
order by votes desc

When run today this is what you get:

[most upvoted post across network]

Keep in mind SEDE is only updated once a week, on Sunday at 03:00 UTC. Checkout the tutorial and the SEDE chatroom.

You must log in to answer this question.

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