SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE MyTable 
( 
    Id INT,
    Name VARCHAR(200)
); 
insert into mytable values (1,'Test');
1 rows affected

insert into mytable values (2,'Test');
1 rows affected

insert into mytable values (3,'Test1');
1 rows affected

insert into mytable values (4,'Test2');
1 rows affected

insert into mytable values (5,'Test2');
1 rows affected

SELECT name, COUNT(name) AS total FROM myTable
GROUP BY name
HAVING COUNT(name) =
(SELECT MAX(sub.total) AS highestCount FROM
(SELECT Name, COUNT(*) AS total
FROM MyTable GROUP BY Name) sub);


name
total


Test
2

Test2
2


WITH stats as
(
SELECT Name
      ,COUNT(id) as count_ids
FROM MyTable
GROUP BY Name
)

SELECT Name
      ,count_ids
FROM
(
SELECT Name
      ,count_ids
      ,RANK() OVER(ORDER BY count_ids DESC) as rank_ -- this ranks all names
FROM stats
) s
WHERE rank_ = 1


Name
count_ids


Test
2

Test2
2