Select the most popular month

This is my sample data. I want to select the most popular month for borrowing.

This is what I’ve tried so far:

SELECT COUNT(borrowdate) 
AS MostPopularMonth
FROM borrower 
GROUP BY borrowdate
ORDER BY borrowdate DESC

Answer

In SQL Server, you can use select top (1):

SELECT TOP (1) YEAR(BorrowDate), Month(BorrowDate), COUNT(*) AS MostPopularMonth
FROM borrower 
GROUP BY YEAR(BorrowDate), Month(BorrowDate)
ORDER BY COUNT(*) DESC;

If there are ties, this returns an arbitrary matching row. If you want all of them, use TOP (1) WITH TIES.