T-SQL Get Max Records By a Column Query

Question

I have a table, I want to get max records by a column. My table:

   id (int) primary key
   topId (int)
   version (int)

Some records have same topId. I want to get max version records which same topId.

Sample records with order:
id:1,2,3,4
topId:1,1,2,2
version:1,2,1,2

I want to get records with query like this:

id:2,4 
topId:1,2    (different topIds)
version:2,2  (Max Versions)

Answer 1

You can use ROW_NUMBER with a CTE:

WITH CTE AS
(
   SELECT id, topId , version,
          rn = row_number() over (partition by topId order by version desc)
   FROM dbo.TableName
)
SELECT id, topId , version
FROM CTE
WHERE rn = 1

If you want all records with the max-version replace row_number with dense_rank.