postgresql 从秩= 1中提取记录

yrdbyhpb 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(146)

我想得到的标题名称，有数字1在排名列。

SELECT title, RANK() OVER(ORDER BY COUNT(*) DESC) rank
FROM rentals as w join copies as e on w.signature = e.signature join books as c on e.idbook = c.idbook
WHERE dateofloan <= CURRENT_DATE - 31
GROUP BY title;

我的代码显示了两列标题、级别
提前感谢您的帮助。

postgresql

来源：https://stackoverflow.com/questions/74171743/extracting-records-from-rank-1

1条答案

按热度按时间

taor4pac1#

子查询并限制为第一个等级：

WITH cte AS (
    SELECT title, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM rentals w
    INNER JOIN copies e ON w.signature = e.signature
    INNER JOIN books c ON e.idbook = c.idbook
    WHERE dateofloan <= CURRENT_DATE - 31
    GROUP BY title
)

SELECT title
FROM cte
WHERE rnk = 1;

赞(0）回复(0）举报 2022-11-04

我来回答

postgresql 从秩= 1中提取记录

1条答案

相关问题

热门标签

最新问答