I have this SQL query to get related comments for each ID in a list:

SELECT comments.id, comments.body, comments.created_at
     , comments.like_count, comments.post_id
FROM   comments
WHERE  comments.is_active AND comments.is_show 
AND    comments.post_id in (1, 7, 9, 11, 3)
GROUP BY comments.id
ORDER BY comments.id
LIMIT 3   <----- this is not working correctly!

I want to get the top 3 comments for each post id in the given list (1, 7, 9, 11, 3).
How to achieve this?

推荐答案

This will nuke the performance of solutions suggested so far:

SELECT c.*
FROM   unnest('{1, 7, 9, 11, 3}'::int[]) AS p(post_id)  -- assuming integer?
CROSS  JOIN LATERAL (
   SELECT id, body, created_at, like_count, post_id
   FROM   comments c
   WHERE  is_active
   AND    is_show 
   AND    c.post_id = p.post_id
   ORDER  BY like_count DESC NULLS LAST  -- assuming this defines "top"?
   LIMIT  3
   ) c
ORDER  BY id, like_count DESC NULLS LAST;  -- see below

Needs an index on (post_id, like_count) to be fast.

Unlike slow solutions with row_number(), which have to scan the whole comments table, this can identify the top 3 comments per post_id from the index cheaply. For big tables, this is faster by orders of magnitude.

See:

If like_count is defined NOT NULL (as it probably should), you can simplify to ORDER BY id, like_count DESC. Else you need DESC NULLS LAST, and the optimal index is on (post_id, like_count DESC NULLS LAST). About that:

Sql相关问答推荐

如何在postgres函数中插入后返回布尔值?

SQL:如何取上一年的平均值?

如何在AWS Athena中 Select JSON数组的最后一个元素?

按二维数组的第一个元素排序

计算不同模式的时间跨度

统计重复记录的总数

SQL 查找 varchar 类型列及其值中多次出现的子字符串

列(值不为空)到其他有序列

SQL 根据前一天的最大值计算每天的值数

SQL 将 Varchar 转换为日期

PostgreSQL中如何提取以特定字符开头的字符串中的所有单词?

SQL 函数 DIFFERENCE 返回有趣的分数

如何在 ClickHouse SQL 中使用 CTE 将邻居语句中的数字作为偏移量传递?

添加一列并根据其他列值进行填充

为数组中的每个元素从表中收集最大整数

As400 (IBM i) SQL 表 QSYS2.SYSTABLES 上的元数据

T-SQL 查询计算日期在其他列中定义的日期之间绑定的行数

BigQuery 错误:SELECT 列表表达式引用 esthetician.LICENSE_TYPE,它既未在 [49:8] 分组也未聚合

使用 SQL 表中的连接列删除重复记录

Postgresql group by 和 jsons 只是在一个数组中