Oracle SQL. Удаление дублей.

Решил зафиксировать решение, так как по-прежнему на собесе это могут спросить. IRL, за всё время работы с Oracle, дубли приходилось удалять только в одном случае - когда я их до этого по ошибке сделал)).

Ок, воспользуемся известной схемой SCOTT* и создадим табличку с дублями.

create table scott.emp_doubles as (
select ename 
from scott.emp e
cross join (select level lvl from dual connect by level < 4) x
)
--Смотрим, что получилось - на каждую строку исходной таблицы 3 значения.
select  q.* 
, count (*) over (partition by ename) qt
from scott.emp_doubles q order by 1

В MS SQL Server дубли можно было бы убрать таким запросом:

with x as (
  select q.*, row_number()over (partition by ename order by ename) rn
  from scott.emp_doubles q
)
delete 
from x where rn > 1

Но в Oracle так не получится. Поэтому приходится использовать "псевдостолбец" rowid.

delete from scott.emp_doubles
where rowid in (
  select rid from (select rowid rid, row_number() over (partition by ename order by ename) rn
  from scott.emp_doubles) where rn > 1
  )

И тут неплохо было бы интервьюеру сказать, что вы не только в курсе про существование "псевдостолбца" rowid. А рассказать, насколько этот rowid является уникальным и "постоянным", почему он может измениться (прочитать об этом можно тут).И ему будет приятно, и вам в зачёт пойдёт.

Если по какой-то причине не нравится использование оконной функции, то есть такие варианты решения:

--(1)
--вместо max можно использовать min
delete from scott.emp_doubles
where rowid not in
  (select max(rowid) rid
  from scott.emp_doubles group by ename)
  
--(2)
delete from scott.emp_doubles t1
where exists 
	(select 1
	from scott.emp_doubles t2
	where t1.rowid > t2.rowid 
	and t1.ename = t2.ename)

*Скрипт для создания схемы SCOTT и наполнения данными при желании можно взять здесь