Finding the maximum score of a PostTypeId for each post - data explorer

Question

I am trying to create a query that will display each user's post Id, PostTypeId, and the maximum score for that posts' type.

For example, for all posts that are questions (PostTypeId = 1) then the maximum score column for all rows where (PostTypeId = 1) will be "22363".

So far I am only getting a table of each posts' score and sorting by DESC to get the maximum score. I see that the maximum score for a question is 22363 and I want to display this number for all questions.

SELECT 
    TOP 100
    Users.Id,
    DisplayName,
    PostTypeId, 
    MAX(CAST(Score AS float)) AS MaxAnswerScore
FROM
    Posts
  INNER JOIN
    Users ON Users.Id = OwnerUserId
GROUP BY
    Users.Id, DisplayName, PostTypeId
ORDER BY
    MaxAnswerScore DESC

I'm trying to do this for all PostTypeIds, not just questions.

Current implementation

Answer 1

You can use row_number() to assign each row a number by the maximum score descending partitioned by the user. Then only take the first record.

SELECT x.id,
       x.displayname,
       x.posttypeid,
       x.score
       FROM (SELECT u.id,
                    u.displayname,
                    p.posttypeid,
                    max(cast(score AS float)) score,
                    row_number() OVER (PARTITION BY u.id
                                       ORDER BY max(cast(score AS float)) DESC) rn
                    FROM users u
                         INNER JOIN posts p
                                    ON p.owneruserid = u.id
                    GROUP BY u.id,
                             u.displayname,
                             p.posttypeid) x
       WHERE x.rn = 1
       ORDER BY x.score DESC;

If you want to see ties, i.e. if a user has their top score in more than one post type, replace row_number() with rank().

---

Edit:

Still not sure what you want, but let's give it another shot:

SELECT u1.id,
       u1.displayname,
       p1.posttypeid,
       max(cast(p1.score AS float)) score,
       (SELECT max(cast(p2.score AS float))
               FROM posts p2
               WHERE p2.posttypeid = p1.posttypeid) maximumscore
       FROM users u1
            INNER JOIN posts p1
                       ON p1.owneruserid = u1.id
       GROUP BY u1.id,
                u1.displayname,
                p1.posttypeid
       ORDER BY p1.posttypeid,
                max(cast(p1.score AS float)) DESC;