algo más que eliminar duplicados en sql server

sirguille · #1 (**permalink**) 02/08/2011, 14:33

Holas, tengo un problema con el que llevo un bueno tiempo dandole vueltas...

Tengo una tabla llamada miTabla, tiene sólo 3 columnas, guid (aleatorio unico, irrepetible), iencucod (código simple), duracion (en segundos, entero):

Quiero eliminar duplicados por el campo "iencucod" pero el registro que quede... tiene que tener la duración mayor.

Osea me quiero quedar con los que estan enmarcados en los rectangulos rojos, parece simple pero no lo es.

Mas o menos mi código es así pero aún le falta algo para que coja el guid correspondiente:

SELECT 'acá falta el guid', iencucod, max(duracion) from miTabla group by iencucod

Gracias para los que me den al menos una guía de cómo tratar este tipo de problemas.

Libras · #2 (**permalink**) 02/08/2011, 15:22

Y si haces algo asi:

Código SQL:

Ver originalCREATE TABLE #temp
(
code INT,
iencucod INT,
duracion INT
)
 
INSERT INTO #temp VALUES (1,2999,1062)
INSERT INTO #temp VALUES (2,2999,610)
INSERT INTO #temp VALUES (3,2999,34)
INSERT INTO #temp VALUES (4,6170,447)
INSERT INTO #temp VALUES (5,15729,1148)
INSERT INTO #temp VALUES (6,15729,241)
INSERT INTO #temp VALUES (7,15729,53)
INSERT INTO #temp VALUES (8,38314,1029)
INSERT INTO #temp VALUES (9,38314,256)
 
 
SELECT code,duracion,iencucod FROM #temp WHERE 
CONVERT(VARCHAR(20),duracion) + '#' + CONVERT(VARCHAR(20),iencucod)  IN 
(
 
SELECT CONVERT(VARCHAR(20),MAX(duracion)) + '#' + CONVERT(VARCHAR(20),iencucod) FROM #temp GROUP BY iencucod
 
)

Saludos!!

iislas · #3 (**permalink**) 03/08/2011, 09:07

¿Nos podrias decir que VERSION - EDICION de SQL Server manejas?

sirguille · #4 (**permalink**) 04/08/2011, 14:49

Ah sí me olvidaba, uso el sql server 2008 express edition, "Libras" voy a revisar el codigo que posteaste, gracias.

iislas · #5 (**permalink**) 04/08/2011, 16:34

Si es 2008, puedes hacer uso de CTE para eliminar los duplicados, te pongo un ejemplo

Código SQL:

Ver originalWITH Dublicates_CTE(Username, accountid)
AS
( 
SELECT UserName,MIN(accountid) 
FROM Accounts
GROUP BY username
HAVING COUNT(*) > 1
)
DELETE FROM Accounts 
WHERE accountid IN ( 
SELECT Accounts.accountid 
FROM Accounts
INNER JOIN Dublicates_CTE 
ON Accounts.Username = Dublicates_CTE.Username
AND Accounts.accountid <> Dublicates_CTE.accountid

sirguille · #6 (**permalink**) 08/08/2011, 15:45

Cita:

Iniciado por Libras

Y si haces algo asi:

Código SQL:

Ver originalCREATE TABLE #temp
(
code INT,
iencucod INT,
duracion INT
)
 
INSERT INTO #temp VALUES (1,2999,1062)
INSERT INTO #temp VALUES (2,2999,610)
INSERT INTO #temp VALUES (3,2999,34)
INSERT INTO #temp VALUES (4,6170,447)
INSERT INTO #temp VALUES (5,15729,1148)
INSERT INTO #temp VALUES (6,15729,241)
INSERT INTO #temp VALUES (7,15729,53)
INSERT INTO #temp VALUES (8,38314,1029)
INSERT INTO #temp VALUES (9,38314,256)
 
 
SELECT code,duracion,iencucod FROM #temp WHERE 
CONVERT(VARCHAR(20),duracion) + '#' + CONVERT(VARCHAR(20),iencucod)  IN 
(
 
SELECT CONVERT(VARCHAR(20),MAX(duracion)) + '#' + CONVERT(VARCHAR(20),iencucod) FROM #temp GROUP BY iencucod
 
)

Saludos!!

Me sirvió el código, gracias. Pero hay un pequeño inconveniente, mi tabla puede variar, puede darse el caso de que tenga duraciones iguales:

insert into #temp values (1,2999,1062)
insert into #temp values (2,2999,1062)
insert into #temp values (3,2999,34)
insert into #temp values (4,6170,447)
insert into #temp values (5,15729,1148)
insert into #temp values (6,15729,241)
insert into #temp values (7,15729,53)
insert into #temp values (8,38314,1029)
insert into #temp values (9,38314,256)

en este caso no me elimina los duplicados 1,2999, 1062 y 2,2999,1062.
Mi objetivo es eliminar duplicados y dejar el de mayor duración, en este último caso dejar cualquiera de los dos ya que tienen duración igual.

Gracias por la ayuda brindada.

Libras · #7 (**permalink**) 08/08/2011, 16:00

Código SQL:

Ver originalCREATE TABLE #temp
    (
    code INT,
    iencucod INT,
    duracion INT
    )
     
    INSERT INTO #temp VALUES (1,2999,1062)
    INSERT INTO #temp VALUES (2,2999,1062)
    INSERT INTO #temp VALUES (3,2999,34)
    INSERT INTO #temp VALUES (4,6170,447)
    INSERT INTO #temp VALUES (5,15729,1148)
    INSERT INTO #temp VALUES (6,15729,241)
    INSERT INTO #temp VALUES (7,15729,53)
    INSERT INTO #temp VALUES (8,38314,1029)
    INSERT INTO #temp VALUES (9,38314,256)
     
 SELECT code,duracion,iencucod FROM
(    
    SELECT ROW_NUMBER() OVER(partition BY iencucod,duracion ORDER BY iencucod,duracion) AS rn, code,duracion,iencucod 
    FROM #temp 
    WHERE
    CONVERT(VARCHAR(20),duracion) + '#' + CONVERT(VARCHAR(20),iencucod)  IN
    (
     
 
        SELECT CONVERT(VARCHAR(20),MAX(duracion)) + '#' + CONVERT(VARCHAR(20),iencucod) AS cadena FROM #temp GROUP BY iencucod
 
     
    )
) t2 WHERE rn=1

No habias aclarado ese punto prueba este codigo y comentas :)

Saludos!

sirguille · #8 (**permalink**) 09/08/2011, 12:24

Cita:

Iniciado por Libras

Código SQL:

Ver originalCREATE TABLE #temp
    (
    code INT,
    iencucod INT,
    duracion INT
    )
     
    INSERT INTO #temp VALUES (1,2999,1062)
    INSERT INTO #temp VALUES (2,2999,1062)
    INSERT INTO #temp VALUES (3,2999,34)
    INSERT INTO #temp VALUES (4,6170,447)
    INSERT INTO #temp VALUES (5,15729,1148)
    INSERT INTO #temp VALUES (6,15729,241)
    INSERT INTO #temp VALUES (7,15729,53)
    INSERT INTO #temp VALUES (8,38314,1029)
    INSERT INTO #temp VALUES (9,38314,256)
     
 SELECT code,duracion,iencucod FROM
(    
    SELECT ROW_NUMBER() OVER(partition BY iencucod,duracion ORDER BY iencucod,duracion) AS rn, code,duracion,iencucod 
    FROM #temp 
    WHERE
    CONVERT(VARCHAR(20),duracion) + '#' + CONVERT(VARCHAR(20),iencucod)  IN
    (
     
 
        SELECT CONVERT(VARCHAR(20),MAX(duracion)) + '#' + CONVERT(VARCHAR(20),iencucod) AS cadena FROM #temp GROUP BY iencucod
 
     
    )
) t2 WHERE rn=1

No habias aclarado ese punto prueba este codigo y comentas :)

Saludos!

Alguna alternativa al row_number() over... me salta el siguiente mensaje: "'row_number' is not a recognized function name", será porque estoy usando la versión express de sql server 2008?

iislas · #9 (**permalink**) 09/08/2011, 13:08

Este codigo, ya fue probado:

CREATE TABLE #temp
(
code int,
iencucod int,
duracion int
)

INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (2,2999,610)
INSERT INTO #temp values (3,2999,34)
INSERT INTO #temp values (4,6170,447)
INSERT INTO #temp values (5,15729,1148)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (7,15729,53)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (9,38314,256)

select * from #temp

WITH CTE (code, iencucod, duracion, DuplicateCount)
AS
(
SELECT code,iencucod, duracion,
ROW_NUMBER() OVER(PARTITION BY code, iencucod, duracion ORDER BY code,iencucod, duracion) AS DuplicateCount
FROM #temp
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

Libras · #10 (**permalink**) 09/08/2011, 13:50

Cita:

Iniciado por iislas

Este codigo, ya fue probado:

CREATE TABLE #temp
(
code int,
iencucod int,
duracion int
)

INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (1,2999,1062)
INSERT INTO #temp values (2,2999,610)
INSERT INTO #temp values (3,2999,34)
INSERT INTO #temp values (4,6170,447)
INSERT INTO #temp values (5,15729,1148)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (6,15729,241)
INSERT INTO #temp values (7,15729,53)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (8,38314,1029)
INSERT INTO #temp values (9,38314,256)

select * from #temp

WITH CTE (code, iencucod, duracion, DuplicateCount)
AS
(
SELECT code,iencucod, duracion,
ROW_NUMBER() OVER(PARTITION BY code, iencucod, duracion ORDER BY code,iencucod, duracion) AS DuplicateCount
FROM #temp
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

Esas common table expresion me encantan, lo malo es que todavia no se utilizarlas bien jejejej

iislas · #11 (**permalink**) 09/08/2011, 14:22

A fuerza de aplicarlas en los proyectos, seran como un vil SELECT el dia de mañana.....Saludos