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

Viewed 172

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?

2 Answers
Related