How to get maximum number of rows grouped by ID in sql
Let's say I have this table: (column: Row
is a number 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 would like to create a new column that will have the largest number in the column Row
grouped by the column ID
for each row. How would I do it? I've worked with MAX (), GROUP BY and some separation, but I get different errors each time. It's hard to guess it right. 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
Use windowed version MAX
:
SELECT ID, Row, State, MAX(Row) OVER (PARTITION BY ID) AS MaxRow
FROM mytable
Demo here
You can join query in table and aggregate table:
SELECT t.*, max_row
FROM t
JOIN (SELECT id, MAX([row]) AS max_row
FROM t
GROUP BY id) agg ON t.id = agg.id
You can create the first query using a group of id and max to get the largest number. Then use that query as a subquery and use the id for the inner join.
Then use the max column from the auxiliary query to get the final result.