locked
Delete duplicate value RRS feed

  • Question

  • User-1687766116 posted

    Hi,

    I have a table:

    id                   date1                  date2

    1                 13970701          13970830

    1                 13970901          13971020

    1                 13971021          13970631

    2                 13970701          13970631

    i want delete duplicate value(id) where smallest date 2.

    result must be:

    id                   date1                  date2

    1                 13971021          13970631

    2                 13970701          13970631

    please help me

    Saturday, March 9, 2019 3:44 PM

All replies

  • User-1174608757 posted

    Hi mehr_83,

    According to your description, I have made a sample here. I suggest you that you could use Min( )to get the min date2  which has the same id, then you could delete the data in table whose date2 is lager than the min. 

    Here is the demo, I hope it could help you.

    My date table:

    sql query:

    DELETE FROM date
    WHERE date2>(
     SELECT MIN(date2)
     FROM date AS csq
     WHERE csq.id=date.id
    
    )
    
    

    It shows as below:

    Best Regards

    Wei Zhang

    Monday, March 11, 2019 2:16 AM
  • User77042963 posted
    CREATE TABLE mytable(
       id    INTEGER  NOT NULL  
      ,date1 INTEGER  NOT NULL
      ,date2 INTEGER  NOT NULL
    );
    INSERT INTO mytable(id,date1,date2) VALUES
     (1,13970701,13970830)
    ,(1,13970901,13971020)
    ,(1,13971021,13970631)
    ,(2,13970701,13970631);
    
    ;with mycte as (
     select * ,   row_number() Over(Partition by id order by date2 ) rn 
     from mytable)
    
     delete mycte
     WHERE rn>1
    
     Select * from mytable
    
    
    
    Drop Table mytable;

    Monday, March 11, 2019 1:59 PM