Clever Geek Handbook

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
+3


source to share


3 answers


Use windowed version MAX

:

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


Demo here

+10


source


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
+2


source


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.

+1


source






More articles:


All Articles