Finding the maximum values of a group count

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply