All we need is an easy explanation of the problem, so here it is.
i am using SQL Server, considering the following table:
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
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:
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.
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 🙂