Delete the single existing record in table OR delete all records except latest?

Question

I'm trying to figure out a way to check for the count of records in a table, and:

• if it's equal to 1, then just delete that one record,
• if it's greater than 1, then delete all except the latest record.

create schema if not exists bv;
create table bv.profile (id bigint primary key, user_id bigint not null);

insert into bv.profile values(2, 100), (1, 101), (3, 100);

I can obviously do this in two queries on my backend like so:

select count(*) from bv.profile where user_id = $1;
-- if count == 1
delete from bv.profile where user_id = $1
-- else if count > 1
delete from bv.profile
where id not in (
  select id
  from bv.profile
  order by id desc
  limit 1
)
and user_id = $1

Is there any way to get this to work in one query though?

After playing around, I came up with this:

delete from bv.profile
where user_id = $1 
and id not in (
  select max(id) from bv.profile
  where user_id = $1
  group by user_id
    having count(*) > 1
);

Not sure how performant this will be, though.

Answer 1

See example

delete from profile
  using (
select *
  ,count(*)over(partition by user_id) cnt
  ,row_number()over(partition by user_id order by id desc) rn
from profile) t 
where t.id=profile.id and (cnt=1 or rn>1)

Assuming that max(id) - last row - order by id desc.

Subquery output is

id	user_id	cnt	rn
3	100	2	1
2	100	2	2
1	101	1	1

Table profile after query execution

id	user_id
3	100