How to get the max row count grouped by the ID in sql

Question

Say I have this table: (column: Row is a count based on the column ID)

ID  | Row  | State | 
1   | 1    | CA    |
1   | 2    | AK    |
2   | 1    | KY    |
2   | 2    | GA    |
2   | 3    | FL    |
3   | 1    | WY    |
3   | 2    | HI    |
3   | 3    | NY    |
3   | 4    | DC    |
4   | 1    | RI    |

I'd like to generate a new column that would have the highest number in the Row column grouped by the ID column for each row. How would I accomplish this? I've been messing around with MAX(), GROUP BY, and some partitioning but I'm getting different errors each time. It's difficult to finesse this correctly. Here's my target output:

ID  | Row  | State | MaxRow
1   | 1    | CA    | 2
1   | 2    | AK    | 2
2   | 1    | KY    | 3
2   | 2    | GA    | 3
2   | 3    | FL    | 3
3   | 1    | WY    | 4
3   | 2    | HI    | 4
3   | 3    | NY    | 4
3   | 4    | DC    | 4
4   | 1    | RI    | 1

Answer 1

Use window version of MAX:

SELECT ID, Row, State, MAX(Row) OVER (PARTITION BY ID) AS MaxRow
FROM mytable

Demo here