Get the records only for the first year by TSQL query

Vivaan Shresth 101 Reputation points
2022-03-18T13:38:17.643+00:00

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?
184547-image.png

I want the result look like below one:
184592-image.png

Thanks.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113K Reputation points
    2022-03-18T13:57:25.28+00:00

    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
    

0 additional answers

Sort by: Most helpful