How to GROUP BY based on newly created MAX() column

Question

My table:

Name	ID	Status
Roger Collins	904	3
Roger John Horspool	915	3
Roger John Shippey	932	3
Roger John Shippey & T.C. Rowell	5341	2
Roger John Shippey & T.C. Rowell	5341	3

Due to poor inputting, some people (e.g. R J Shippey & T C Rowell) have more than 1 'status' value. What I would like to do is do a GROUP BY on this table, but on the MAX value of status. So the table would look like this:

Name	ID	Max_Status
Roger Collins	904	3
Roger John Horspool	915	3
Roger John Shippey	932	3
Roger John Shippey & T.C. Rowell	5341	3

I have managed to make a new column with the Max_Status, like so:

Name	ID	Status	Max_Status
Roger Collins	904	3	3
Roger John Horspool	915	3	3
Roger John Shippey	932	3	3
Roger John Shippey & T.C. Rowell	5341	2	3
Roger John Shippey & T.C. Rowell	5341	3	3

Using this code:

SELECT Name, 
        ID,
       Status,  
       MAX(Status) OVER(PARTITION BY Name) AS MaxStatus 

FROM [dbo].[TaskStatus_View]

But now I can't do a groupby based on the Max_Status column, because (from what I understand) I am creating it after I am accessing the original database so it just says invalid column name. So I am unsure on the next step. I have tried to put the Partition line into a subquery, but I can't really grasp this intuitively.

Answer 1

Here is how you can use your query as a subquery and perform the grouping

SELECT Name,ID,MaxStatus FROM
(
  SELECT Name,ID,Status,MAX(Status) OVER(PARTITION BY Name) AS MaxStatus 
FROM [dbo].[TaskStatus_View]
) t
GROUP BY Name,ID,MaxStatus

fiddle

Name	ID	MaxStatus
Roger Collins	904	3
Roger John Horspool	915	3
Roger John Shippey	932	3
Roger John Shippey & T.C. Rowell	5341	3