Finding the maximum values of a group count



  • 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?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2