FAQ
Good Day All,


I have a table with more than 100 columns of data. In some cases,
several rows may have duplicate data except for 3 or 4 columns.
Is there an easy way to find those rows?


Thanks in advance for any assistance.


Bill


William Gaston
Systems Designer / Information Technology
USG Corporation
Chicago, Il 60606
(312) 606-3851
wgaston_at_usg.com

Search Discussions

  • Lex de Haan at Apr 15, 2005 at 3:47 pm
    are these "3 or 4 columns" always the same columns, for all rows, or do
    you want to identify the rows that are "almost identical" in general?

    in case you mean the former, that's relatively easy. the latter will take
    some more intelligence :-)

    cheers,
    Lex.
    Good Day All,

    I have a table with more than 100 columns of data. In some cases,
    several rows may have duplicate data except for 3 or 4 columns.
    Is there an easy way to find those rows?

    Thanks in advance for any assistance.

    Bill

    William Gaston
    Systems Designer / Information Technology
    USG Corporation
    Chicago, Il 60606
    (312) 606-3851
    wgaston_at_usg.com

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Tapan Trivedi at Apr 15, 2005 at 3:55 pm
    Put a unique constraint on the table .
    Enable it.
    Put the exceptions into the exceptions table.
    $ORACLE_HOME/rdbms/admin/utlexcpt.sql to create the exceptions table.


    $Alter table table_name enable constraint unique_constraint_name exceptions into exceptions;


    The exceptions table will have the row_ids of the rows which violate the constraint.


    HTH,

    Tapan Trivedi


    Lex de Haan wrote:
    are these "3 or 4 columns" always the same columns, for all rows, or do
    you want to identify the rows that are "almost identical" in general?

    in case you mean the former, that's relatively easy. the latter will take
    some more intelligence :-)

    cheers,
    Lex.
    Good Day All,

    I have a table with more than 100 columns of data. In some cases,
    several rows may have duplicate data except for 3 or 4 columns.
    Is there an easy way to find those rows?

    Thanks in advance for any assistance.

    Bill

    William Gaston
    Systems Designer / Information Technology
    USG Corporation
    Chicago, Il 60606
    (312) 606-3851
    wgaston_at_usg.com

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    Tapan Trivedi
    Senior Oracle DBA
    916 613 1921

    test'; ">

    test'; ">

    --
    http://www.freelists.org/webpage/oracle-l
  • Connor McDonald at Apr 16, 2005 at 7:11 am
    something like

    select *
    from (
    select *,

    row_number() over ( partition by col1, col2, col3, col4 order by ) as r
    from my_table
    )
    where r > 1

    show the rows that have a duplicated based on col1,2,3,4

    htht
    connor

    Lex de Haan wrote:
    are these "3 or 4 columns" always the same columns, for all rows, or do
    you want to identify the rows that are "almost identical" in general?

    in case you mean the former, that's relatively easy. the latter will take
    some more intelligence :-)

    cheers,
    Lex.
    Good Day All,

    I have a table with more than 100 columns of data. In some cases,
    several rows may have duplicate data except for 3 or 4 columns.
    Is there an easy way to find those rows?

    Thanks in advance for any assistance.

    Bill

    William Gaston
    Systems Designer / Information Technology
    USG Corporation
    Chicago, Il 60606
    (312) 606-3851
    wgaston_at_usg.com

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    Connor McDonald
    Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
    Co-author: "Oracle Insight - Tales of the OakTable"

    web: http://www.oracledba.co.uk
    web: http://www.oaktable.net
    email: connor_mcdonald_at_yahoo.com

    "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

    Send instant messages to your online friends http://uk.messenger.yahoo.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 15, '05 at 3:33p
activeApr 16, '05 at 7:11a
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase