Finding the maximum values of a group count

Question

i am using SQL Server, considering the following table:

Content Table

title	topic	id_user
Title 1	Topic1	1
Title 2	Topic1	1
Title 3	Topic2	2
Title 4	Topic1	3
Title 5	Topic1	1
Title 6	Topic1	3
Title 7	Topic2	2

When I run I get the following:

SELECT topic, id_user, COUNT(*) AS total
FROM Content
GROUP BY topic, id_user
ORDER BY total DESC

Result

topic	id_user	total
Topic1	1	3
Topic1	3	2
Topic2	2	2

In this case there are two users (there could be more) with Topic1, I only want to show the one with the highest total count value of their respective Topic

For example get the following result:

Expected Result

topic	id_user	total
Topic1	1	3
Topic2	2	2

Any clue how this could be done?

Answer 1

DECLARE @content TABLE
(
    title VARCHAR(10) NOT NULL,
    topic VARCHAR(10) NOT NULL,
    id_user INT NOT NULL
);

INSERT INTO @content (title, topic, id_user)
VALUES
(N'Title 1', N'Topic 1', 1),
(N'Title 2', N'Topic 1', 1),
(N'Title 3', N'Topic 2', 2),
(N'Title 4', N'Topic 1', 3),
(N'Title 5', N'Topic 1', 1),
(N'Title 6', N'Topic 1', 3),
(N'Title 7', N'Topic 2', 2);

-- Your solution for reference
SELECT   topic,
         id_user,
         COUNT(*) AS total
FROM     @content
GROUP BY topic,
         id_user
ORDER BY total DESC;

-- Desired solution with undefined behaviour if there are multiple users with the highest total value
WITH cte
AS (SELECT   topic,
             id_user,
             COUNT(*) AS total,
             ROW_NUMBER() OVER (PARTITION BY topic ORDER BY COUNT(*) DESC) AS num
    FROM     @content
    GROUP BY topic,
             id_user)
SELECT cte.topic,
       cte.id_user,
       cte.total
FROM   cte
WHERE  cte.num = 1;

You could use the ROW_NUMBER() windowed function to find the users with the biggest total.

I only want to show the one with the highest total count value of their respective Topic

This implies that there is in some way made sure, that there is always one user with the highest total.