none
Eliminar registros duplicados con condiciones RRS feed

  • Pregunta

  • Buenos días,

    Lo que pasa tengo una consulta, en la cual tengo que eliminar unos registros, pero no he podido hacerlo porque la condicion es que me muestre y luego elimine los registros que estan repetidos, pero el que tenga la cantidad mayor de los que estan repetidos, osea si tengo el articulo caf-18017, que elimine el que tiene cantidad 10.

    SELECT     consecutivo, codbodega, codigo, cantidad, valor, costo, codlote, idmvto, entrada
    FROM       tblmvtoinventarios
    WHERE     (consecutivo = '311') AND (entrada = 'true') OR
              (consecutivo = '306') AND (entrada = 'false')
    ORDER BY codigo

    Resultado

    consecutivo, codbodega, codigo, cantidad, valor, costo, codlote, idmvto, entrada
    306,PV-ENV,CAF-18017,10.0,0.00,120.00,0,11582,False
    306,PV-ENV,CAF-18017,2.0,0.00,24.00,0,13524,False
    306,PV-ENV,ING-10039,0.002000,0.00,2.43,0,13519,False
    306,PV-ENV,ING-10039,0.010000,0.00,12.14,0,11577,False
    306,PV-ENV,ING-10053,5.0,0.00,425.00,0,11579,False
    306,PV-ENV,ING-10053,1.0,0.00,85.00,0,13521,False
    306,PV-ENV,ING-10058,10.0,0.00,64.00,0,13520,False
    306,PV-ENV,ING-10058,50.0,0.00,320.00,0,11578,False
    306,PV-ENV,ING-10060,0.050000,0.00,0.21,0,11580,False
    306,PV-ENV,ING-10060,0.010000,0.00,0.04,0,13522,False
    306,PV-ENV,ME-14065,1.0,0.00,19.07,0,13525,False
    306,PV-ENV,ME-14065,5.0,0.00,95.35,0,11583,False
    306,PV-ENV,ME-14078,5.0,0.00,456.90,0,11581,False
    306,PV-ENV,ME-14078,1.0,0.00,91.38,0,13523,False
    306,PV-ENV,MOD-00001,10.0,0.00,393.50,0,13526,False
    306,PV-ENV,MOD-00001,50.0,0.00,1967.50,0,11584,False
    306,PV-ENV,PV-01035,0.5,0.00,3732.87,0,11576,False
    306,PV-ENV,PV-01035,0.1,0.00,746.57,0,13518,False
    311,PV-ENV,PV-03320,1.0,0.00,1750.78,0,13517,True

    Gracias

    Juank

    
    
    
    jueves, 31 de mayo de 2012 15:48

Respuestas

  • Puedes hacer uso de la funcion ROW_NUMBER para enumerar filas por cada grupo de codigos, luego puedes eliminar las filas donde el consecutivo calculado es mayor uno. Puedes adicionar la clausula OUTPUT para mostrar las filas eliminadas.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    DECLARE @T TABLE (
    consecutivo int, 
    codbodega char(6), 
    codigo varchar(15), 
    cantidad numeric (8, 2), 
    valor numeric (8, 6), 
    costo numeric (8, 2), 
    codlote smallint, 
    idmvto int, 
    entrada varchar(5)
    );
    
    INSERT INTO @T
            (
             consecutivo,
             codbodega,
             codigo,
             cantidad,
             valor,
             costo,
             codlote,
             idmvto,
             entrada
            )
    VALUES
    	(306,'PV-ENV','CAF-18017',10.0,0.00,120.00,0,11582,'False'),
    	(306,'PV-ENV','CAF-18017',2.0,0.00,24.00,0,13524,'False'),
    	(306,'PV-ENV','ING-10039',0.002000,0.00,2.43,0,13519,'False'),
    	(306,'PV-ENV','ING-10039',0.010000,0.00,12.14,0,11577,'False'),
    	(306,'PV-ENV','ING-10053',5.0,0.00,425.00,0,11579,'False'),
    	(306,'PV-ENV','ING-10053',1.0,0.00,85.00,0,13521,'False'),
    	(306,'PV-ENV','ING-10058',10.0,0.00,64.00,0,13520,'False'),
    	(306,'PV-ENV','ING-10058',50.0,0.00,320.00,0,11578,'False'),
    	(306,'PV-ENV','ING-10060',0.050000,0.00,0.21,0,11580,'False'),
    	(306,'PV-ENV','ING-10060',0.010000,0.00,0.04,0,13522,'False'),
    	(306,'PV-ENV','ME-14065',1.0,0.00,19.07,0,13525,'False'),
    	(306,'PV-ENV','ME-14065',5.0,0.00,95.35,0,11583,'False'),
    	(306,'PV-ENV','ME-14078',5.0,0.00,456.90,0,11581,'False'),
    	(306,'PV-ENV','ME-14078',1.0,0.00,91.38,0,13523,'False'),
    	(306,'PV-ENV','MOD-00001',10.0,0.00,393.50,0,13526,'False'),
    	(306,'PV-ENV','MOD-00001',50.0,0.00,1967.50,0,11584,'False'),
    	(306,'PV-ENV','PV-01035',0.5,0.00,3732.87,0,11576,'False'),
    	(306,'PV-ENV','PV-01035',0.1,0.00,746.57,0,13518,'False'),
    	(311,'PV-ENV','PV-03320',1.0,0.00,1750.78,0,13517,'True');
    
    SELECT *
    FROM @T
    ORDER BY codigo, cantidad;
    
    WITH R AS (
    SELECT
    	*,
    	ROW_NUMBER() OVER(PARTITION BY codigo ORDER BY cantidad) AS rn
    FROM
    	@T
    )
    DELETE R
    OUTPUT DELETED.*
    WHERE rn > 1;
    
    SELECT *
    FROM @T
    ORDER BY codigo;
    GO


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Juank18 jueves, 31 de mayo de 2012 18:56
    jueves, 31 de mayo de 2012 17:46

Todas las respuestas

  • > que me muestre y luego elimine los registros que estan repetidos

    Pudieras decirnos el criterio para considerar dos filas como repetidas?


    AMB

    Some guidelines for posting questions...

    jueves, 31 de mayo de 2012 17:05
  • HOla, yo considero como repetidas las filas, cuando el codigo es repetido para mi la fila esta repetida, se elimina el mayor

    jueves, 31 de mayo de 2012 17:24
  • Puedes hacer uso de la funcion ROW_NUMBER para enumerar filas por cada grupo de codigos, luego puedes eliminar las filas donde el consecutivo calculado es mayor uno. Puedes adicionar la clausula OUTPUT para mostrar las filas eliminadas.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    DECLARE @T TABLE (
    consecutivo int, 
    codbodega char(6), 
    codigo varchar(15), 
    cantidad numeric (8, 2), 
    valor numeric (8, 6), 
    costo numeric (8, 2), 
    codlote smallint, 
    idmvto int, 
    entrada varchar(5)
    );
    
    INSERT INTO @T
            (
             consecutivo,
             codbodega,
             codigo,
             cantidad,
             valor,
             costo,
             codlote,
             idmvto,
             entrada
            )
    VALUES
    	(306,'PV-ENV','CAF-18017',10.0,0.00,120.00,0,11582,'False'),
    	(306,'PV-ENV','CAF-18017',2.0,0.00,24.00,0,13524,'False'),
    	(306,'PV-ENV','ING-10039',0.002000,0.00,2.43,0,13519,'False'),
    	(306,'PV-ENV','ING-10039',0.010000,0.00,12.14,0,11577,'False'),
    	(306,'PV-ENV','ING-10053',5.0,0.00,425.00,0,11579,'False'),
    	(306,'PV-ENV','ING-10053',1.0,0.00,85.00,0,13521,'False'),
    	(306,'PV-ENV','ING-10058',10.0,0.00,64.00,0,13520,'False'),
    	(306,'PV-ENV','ING-10058',50.0,0.00,320.00,0,11578,'False'),
    	(306,'PV-ENV','ING-10060',0.050000,0.00,0.21,0,11580,'False'),
    	(306,'PV-ENV','ING-10060',0.010000,0.00,0.04,0,13522,'False'),
    	(306,'PV-ENV','ME-14065',1.0,0.00,19.07,0,13525,'False'),
    	(306,'PV-ENV','ME-14065',5.0,0.00,95.35,0,11583,'False'),
    	(306,'PV-ENV','ME-14078',5.0,0.00,456.90,0,11581,'False'),
    	(306,'PV-ENV','ME-14078',1.0,0.00,91.38,0,13523,'False'),
    	(306,'PV-ENV','MOD-00001',10.0,0.00,393.50,0,13526,'False'),
    	(306,'PV-ENV','MOD-00001',50.0,0.00,1967.50,0,11584,'False'),
    	(306,'PV-ENV','PV-01035',0.5,0.00,3732.87,0,11576,'False'),
    	(306,'PV-ENV','PV-01035',0.1,0.00,746.57,0,13518,'False'),
    	(311,'PV-ENV','PV-03320',1.0,0.00,1750.78,0,13517,'True');
    
    SELECT *
    FROM @T
    ORDER BY codigo, cantidad;
    
    WITH R AS (
    SELECT
    	*,
    	ROW_NUMBER() OVER(PARTITION BY codigo ORDER BY cantidad) AS rn
    FROM
    	@T
    )
    DELETE R
    OUTPUT DELETED.*
    WHERE rn > 1;
    
    SELECT *
    FROM @T
    ORDER BY codigo;
    GO


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Juank18 jueves, 31 de mayo de 2012 18:56
    jueves, 31 de mayo de 2012 17:46
  • Gracias por su aporte
    jueves, 31 de mayo de 2012 18:56