How to get all records having maximum value in a SQL Server table

Question

I've below data in MigratoryBirds table in SQL Server

birdType
1
4
4
4
5
5
5
3

SQL script to create above table:

/****** Object:  Table [dbo].[migratoryBirds]    Script Date: 20-Jul-17 8:01:02 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[migratoryBirds](
    [birdType] [int] NULL
) ON [PRIMARY]

GO

My objective is to get those bird types which have highest frequency in the table. If I write a stored procedure and have the freedom to write several SQL queries then it is not that difficult but I'm trying to achieve it using single SQL query. I'm trying to see if Having clause of SQL server can be of any help to me.

So the first query I wrote was:

select birdType, count(1) AS [birdCount]
from migratorybirds
group by birdType

which gives below output

birdType birdCount
1        1
3        1
4        3
5        3

As this is a case of aggregation, so I thought Having clause can help me here to filter out the records which have the highest frequency 3. birdType 4 and 5 are having highest frequency 3.

So, I enhanced my query like this:

select birdType, count(1) AS [birdCount]
from migratorybirds
group by birdType
having count(1) > Max(count(1))

which gives below error:

Msg 130, Level 15, State 1, Line 18 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can anyone help me in achieving the same?

Answer 1

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

select top (1) with ties birdType, count(1) AS birdCount
from migratorybirds
group by birdType
order by count(1) desc;

Answer 2

In practice I would probably go with Gordon's answer, but if you wanted to continue with your current approach you could try the following:

WITH cte AS (
    SELECT birdType, COUNT(1) AS [birdCount]
    FROM migratorybirds
    GROUP BY birdType
)

SELECT *
FROM cte
WHERE birdCount = (SELECT MAX(birdCount) FROM cte)

You need an actual subquery in the WHERE clause if you want to use MAX in this way. Note that this solution requires two queries whereas Gordon's answer requires only one.