Include an additional column in select query with group by using a different column

Question

I need some help for a SQL query.

Given the following table:

+---+------+--------------+
|  ID  |  Team  |  Year   |
--------------------------+
|  1   |   A    |  2018   |
|  1   |   B    |  2015   |
|  1   |   C    |  2013   |
|  2   |   A    |  2019   |
|  2   |   B    |  2018   |
|  3   |   A    |  2017   |
|  3   |   D    |  2018   |
---------------------------

How can I group these rows by ID using MIN(Year) to get the following result:

+---+------+--------------+
|  ID  |  Team  |  Year   |
--------------------------+
|  1   |   C    |  2013   |
|  2   |   B    |  2018   |
|  3   |   A    |  2017   |
---------------------------

I'm using TSQL.

Thanks!

Answer 1

use corelated subquery

    select a.* from table a
    where a.year = (
        select min(year) from table b where a.id=b.id
    )

or rownumber()

    select a.* from (
        select * , row_number() over(partition by id order by year) rn from table_name
    ) as a
    where a.rn=1