How to get rid of an additional duplicate record using rank() function in sql?

Question

I have the following 2 records:

key	start_dt	end_dt	id	status
12	2020-8-12	2300-01-01	1	active
12	1998-9-23	2014-9-23	2	active

This is the row_number() function I currently have:

row_number() over (partition by id order by status, end_dt desc) rnk

I want to know what I need to add to the above row_number() function so that only the top record is showing:

key	start_dt	end_dt	id	status
12	2020-8-12	2300-01-01	1	active

Answer 1

Here's how I would do it for SQL Server. For BigQuery, your syntax will differ a little but I'm sure the structure will be the same.

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