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.
