如何从SQL查询结果中提取rank列值为1的title字段？

有两种常用方式可以达成需求，都是基于原查询的结果做二次筛选：

方法1：子查询 #

把原查询作为子查询，在外层添加rank = 1的过滤条件：

SELECT title
FROM (
    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
) ranked_titles
WHERE rank = 1;

方法2：CTE（公共表表达式） #

用CTE封装原查询逻辑，再筛选排名为1的记录，可读性更强：

WITH ranked_titles AS (
    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
)
SELECT title
FROM ranked_titles
WHERE rank = 1;

补充说明 #

两种方法逻辑一致：先通过原查询计算每个title的排名，再过滤出排名为1的结果。如果有多个title的借阅量并列第一，这两种方法都会返回所有并列第一的title，完全匹配RANK()函数的特性（并列排名会跳过后续名次）。

内容的提问来源于stack exchange，提问作者Ashtart