CODEWITHSUNDEEP
sqlsql-serverselectfiltergroup-by
LaRae White
LaRae White

Reputation: 1272

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

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

Upvotes: 4

Views: 4202

Answers (3)

Juan
Juan

Reputation: 1382

You can create first a query using group by id and max to get the highest number. Then use this query as a sub query and use the id to inner join.

Then use the max column from the sub query to obtain your final result.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

Use window version of MAX:

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

Demo here

Upvotes: 10

Mureinik
Mureinik

Reputation: 312404

You could join between a query on the table and an 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

Upvotes: 2

Related Questions