By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #a (
[key] int
, start_dt date
, end_dt date
, id int
, status varchar(20)
)
insert into #a
values
(12, {d '2020-08-12'}, {d '2300-01-01'}, 1, 'active')
, (12, {d '1998-09-23'}, {d '2014-09-23'}, 2, 'active')
;
with
b as (
select
[key]
, start_dt
, end_dt
, id
, status
, max(end_dt) over (partition by [key]) as maxenddt
from #a
)
select
[key]
, start_dt
, end_dt
, id
, status
from b
where end_dt = maxenddt
| key | start_dt | end_dt | id | status |
|---|---|---|---|---|
| 12 | 2020-08-12 | 2300-01-01 | 1 | active |