SQL - Choose which duplicate value to select

Question

I have a table with a datetime, value and user. This table has multiple rows for the same datetime but with a different user and value.

I want select distinct datetime with the corresponding value and user. Where there is a duplicate datetime with different users, the value that user2 has input should be prioritised.

Table 1
-----------------
DateTime|    Value|    User
--------|---------|---------
1/1/17  |       10|    User1
2/1/17  |       30|    User1
3/1/17  |       10|    User1
1/1/17  |       90|    User2
2/1/17  |       80|    User2     

So from the above, I would end up with

1/1/17   |     90| User2
2/1/17   |     80| User2
3/1/17   |     10| User1

I'm sure there is a simple answer to this but I can't for the life of me work out how to do it!

Any help greatly appreciated.

Thanks

Answer 1

Not quite simple! Using window functions and common table expressions

; with x as (
select [DateTime], value, [User], row_num = row_number() over(partition by [DateTime] order by [User] desc) from Table1 
)
select x.* from x where row_num = 1