extracting records from rank = 1-CodePudding

I would like to get name of title that have the number 1 in the rank column.

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;

My code shows two columns title, rank

Thank you in advance for your help.

CodePudding user response：

Subquery and restrict to the first rank:

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;