Remove Duplicated row on a iseries table

  • I have a table similar to below

    CustomerReferencedatecard_number created_by

    John 1122334512008-02-0148001796Sen

    John1122334512008-02-0148001796Sen

    Eddy1235656542008-03-2648001799Mark

    Mary1245781282009-10-2748001850Mark

    From the above table I need to remove all duplicated rows leaving one row of each record on the table.i.e I need to have one record of customer ‘John’ on the table. The duplicated rows having same values on all columns and thus difficult to use max or min.

    Thanks

  • You could try something like:

    |

    CREATE TABLE #T(Date_Stamp DATETIME,KW1 DECIMAL(5,1), KW2 DECIMAL(5,1))

    INSERT INTO #T

    SELECT '12/10/2010', 5.3, 3.1 UNION ALL

    SELECT '12/10/2010', 5.3, 3.1 UNION ALL

    SELECT '12/9/2010', 4, 2 UNION ALL

    SELECT '12/8/2010', 3, 1 UNION ALL

    SELECT '12/7/2010', 7.4, 5 UNION ALL

    SELECT '12/7/2010', 7.4, 5 UNION ALL

    SELECT '12/7/2010', 7.4, 5

    with cte

    as (select row_number() over(partition by Date_Stamp,KW1,KW2 order by Date_Stamp) as rn,

    KW1,KW2

    from #T)

    SELECT * FROM cte

    duplicate entries would have a row_number greater than 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply