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相关问答推荐

使用 SQL 根据列表中的顺序 Select 值

任何查询的数组列的 Postgres 索引类型

在 SQL 中搜索 WHERE 值为 SELECT,为什么现在可以工作?

SQL 查询通过其特定的最新操作查找用户

什么时候值得使用数据库?

SQL 中 DateTime 字段的时间部分

设置一个空的 DateTime 变量

使用 TSQL 确定表的主键

我想显示所有具有指定列名的表

存储过程与意见

如何判断字符串是否是唯一标识符?

SQL Server替换,删除特定字符后的所有内容

如何替换 oracle 数据库列中的特定值?

如何从 Oracle 中的正则表达式中提取组?

将参数数组传递给存储过程

SQL Server 中的条件 WHERE 子句

如何在 Postgres 中判断数组是否为空

如何在 Oracle SQL 中处理单引号

即使有事务回滚,SQL 标识(自动编号)也会增加

数据为空.不能对 Null 值调用此方法或属性