repetir - sql eliminar registros repetidos dejando solo 1

Encontrar filas con valores duplicados en una columna

Question

Tengo una tabla author_data :

 author_id | author_name
 ----------+----------------
 9         | ernest jordan
 14        | k moribe
 15        | ernest jordan
 25        | william h nailon 
 79        | howard jason
 36        | k moribe

Ahora necesito el resultado como:

 author_id | author_name                                                  
 ----------+----------------
 9         | ernest jordan
 15        | ernest jordan     
 14        | k moribe 
 36        | k moribe

Es decir, necesito el author_id para que los nombres tengan apariencias duplicadas. He intentado esta declaración:

select author_id,count(author_name)
from author_data
group by author_name
having count(author_name)>1

Pero no está funcionando. ¿Cómo puedo conseguir esto?

Answer 1

Sugiero una función de ventana en una subconsulta:

SELECT author_id, author_name  -- omit the name here, if you just need ids
FROM (
   SELECT author_id, author_name
        , count(*) OVER (PARTITION BY author_name) AS ct
   FROM   author_data
   ) sub
WHERE  ct > 1;

Reconocerá el count() funciones agregadas básicas count() . Se puede convertir en una función de ventana al agregar una cláusula OVER , como cualquier otra función de agregado.

De esta manera cuenta las filas por partición . Voilá.

En versiones anteriores sin funciones de ventana (v.8.3 o anterior) - o en general - esta alternativa funciona bastante rápido:

SELECT author_id, author_name  -- omit name, if you just need ids
FROM   author_data a
WHERE  EXISTS (
   SELECT 1
   FROM   author_data a2
   WHERE  a2.author_name = a.author_name
   AND    a2.author_id <> a.author_id
   );

Si le preocupa el rendimiento, agregue un índice en author_name .

Answer 2

Ya estás a mitad de camino. Solo necesita usar los Author_IDs identificados y obtener el resto de los datos.

prueba esto..

SELECT author_id, author_name
FROM author_data
WHERE author_id in (select author_id
        from author_data
        group by author_name
        having count(author_name)>1)

Answer 3

Puede unirse a la tabla en sí mismo, lo que se puede lograr con cualquiera de las siguientes consultas:

SELECT a1.author_id, a1.author_name
FROM authors a1
CROSS JOIN authors a2
  ON a1.author_id <> a2.author_id
  AND a1.author_name = a2.author_name;

-- 9 |ernest jordan
-- 15|ernest jordan
-- 14|k moribe
-- 36|k moribe

--OR

SELECT a1.author_id, a1.author_name
FROM authors a1
INNER JOIN authors a2
  WHERE a1.author_id <> a2.author_id
  AND a1.author_name = a2.author_name;

-- 9 |ernest jordan
-- 15|ernest jordan
-- 14|k moribe
-- 36|k moribe