Get the records only for the first year by TSQL query

Question

Hi,
I have the below table, I want the rows by each year which includes only first year, for instance 2020 and remove 2021 and 2022. Please help on tsql code?

I want the result look like below one:

Thanks.

Answer 1

Try this query:

; with Q as
(
    select *, rank() over (partition by Codenumber order by Year) as r from MyTable
)
select ID, M_ID, Codenumber, Year, Month
from Q
where r = 1
order by ID

If you want to remove the rows:

delete from t
from (select rank() over (partition by Codenumber order by Year) r from MyTable) t
where r > 1