# List best posts forum-wide based on likes

**URL:** https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2017年十月13日 17:46 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025 "2017-10-13T17:46:31Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![LeoB](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/leob/32/42365_2.png) [@LeoB](https://meta.discourse.org/u/LeoB)
#### Post date: [2017年十月13日 17:46 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/1 "2017-10-13T17:46:31Z")

</div>

I’m looking for a way for me, as admin, to see a list of the most popular posts, forum-wide, based on the number of likes.

Basically a **posts** -centered version of…

- [Discourse Meta](http://meta.discourse.org/users), which lists and allows sorting of **users** , or
- [Discourse Meta](http://meta.discourse.org/top), which lists and allows sorting of **topics**.

My use case is that I’d like to identify posts that were judged most valuable by the community, so I can use them the basis for articles I write for readers of my email newsletter who are not on the forum.

---

<div class="post-metadata">

### Author: ![ChrisBeach](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/chrisbeach/32/214628_2.png) [@ChrisBeach](https://meta.discourse.org/u/ChrisBeach)
#### Post date: [2017年十月13日 19:00 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/2 "2017-10-13T19:00:57Z")

</div>

I use the following query in the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin:

```plaintext
SELECT p.id as post_id, p.like_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN users u ON u.id = p.user_id
WHERE NOT u.blocked
ORDER BY p.like_count DESC, p.created_at ASC
LIMIT 10

```

Also, consider using “score” rather than like count. Score takes a weighted average of different measures of post value.

---

<div class="post-metadata">

### Author: ![LeoB](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/leob/32/42365_2.png) [@LeoB](https://meta.discourse.org/u/LeoB)
#### Post date: [2017年十月13日 21:45 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/3 "2017-10-13T21:45:37Z")

</div>

Very cool. Thank you, Chris. I will try that.

---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [2019年十一月19日 19:38 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/4 "2019-11-19T19:38:50Z")

</div>



---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [2023年八月19日 23:32 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/5 "2023-08-19T23:32:03Z")

</div>



---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [2023年九月18日 23:32 UTC](https://meta.discourse.org/t/list-best-posts-forum-wide-based-on-likes/72025/6 "2023-09-18T23:32:47Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.