How to just keep latest records in SQL server?

Here is my case,

Table name: EMP_Detail
Records:
ID          Name     Statue     ChangeDate
1111        Apple    Active     2017-01-01
1111        Apple    Active     2017-03-07
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2017-02-05
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Trying to remove the active and out of date records and keep table like this:

ID          Name     Statue     ChangeDate
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Thanks a lot!

You can use row_number() and a CTE to delete from the source table.

;with cte as(
   select 
        *,
       RN = row_number() over (partition by [Name], [Status] order by [ChangeDate] desc, ID desc)
   from EMP_Detail)

delete from cte
where RN != 1;

StackExchange Database Administrators Q#202472, answer score: 4