AS/400: Remove duplicate rows from table

Tags:
AS/400
I have a table created through Create Table query. Now there are duplicate records in this table.
eg:  field1    field2
       100      AAAAA
100     BBBBB
        200    CCCCC
        200    DDDDD
        300    EEEEE
400    FFFFF
        400    GGGGG.
Now, I need to remove all duplicate from this table but keeping only first row (or 1 record) for each duplicate for Field1.
Can anyone help. Can anyone help me with the query?


Software/Hardware used:
sql400
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    This seems more like a homework question than an actual problem. Can you show what you have tried already?

    If it's a real-life problem, we don't want to waste time by suggesting things that you've already tried.
    35,040 pointsBadges:
    report
  • Supriyoas400
    i have not provided actual data but a simpler version of the actual issue..but i am not able to figure out what kind of query will satisfy my need : i need to keep 1 row for each duplicate record and remove remaining duplicates.


    250 pointsBadges:
    report
  • TheRealRaven
    What is the OS version? Features in later versions make it easier, but it's possible even in the earliest SQL releases. It just gets more complicated the earlier it goes.
    35,040 pointsBadges:
    report
  • ToddN2000
    I'd do this with SQL. Try it and if you still have a problem, post your code and we will point you in the right direction.
    132,590 pointsBadges:
    report
  • TheRealRaven
    It'll be SQL. An example difference between older and newer releases could be whether the older RRN() function should be used or if ROW_NUMBER() would work. In an older case, it might be necessary to create a VIEW, and then a DELETE with a sub-SELECT might reference the VIEW to isolate rows to keep.
    35,040 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: