SQL Server: how to avoid data duplication?

Question

I want to request the image above.

The left image is the source data, the right image is the request data.

select distinct ID, Nickname, Revision from test_table 

This request is not displayed above.

How to avoid data duplication?

Answer 1

If SQL Server using the window function ROW_NUMBER in a subquery:

 select t.id, t.nickname, t.revision from ( select t.*, row_number() over ( partition by t.id order by t.revision desc ) rn from your_table t ) t where rn = 1; 

Or using TOP with ties with ROW_NUMBER :

 select top 1 with ties * from your_table order by row_number() over ( partition by id order by revision desc ) 

If MySQL:

 select t.* from your_table t inner join ( select id, MAX(revision) revision from your_table group by id ) t1 on t.id = t1.id and t.revision = t1.revision; 

Answer 2

Another trick using TOP 1 with TIES

 SELECT Top 1 with ties * FROM your_table t Order by row_number() over (partition BY t.id order by t.revision DESC) 

Answer 3

 select distinct ID, Nickname, MAX(Revision) from test_table group by ID