Results 1 to 11 of 11

Thread: duplicate rows

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: duplicate rows

    If I have to delete duplicate rows from a table, then I can give this:
    delete from tabname where rowid not in
    (select max(rowid) from tabname group by id)
    How do I select select duplicate rows from a table?
    I can use distinct but didnt know how to do it
    Like if i say
    select count(distinct col1) from tabname;
    will give me the count for just col1.

    col1 col2 col3 col4 col5 col6 col7
    ===================================
    aa dd ee 44 55 66 mm *
    aa dd ee 44 55 66 mm *--> I was to select such rows
    aa oo pp 44 55 mm 55
    I have about 100 columns and want to check the count of duplicate rows.
    Can anyone please give me some idea. Thanks in advance.

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Try this:

    select * from (select col1, col2, col3, col4, col5, count(*)
    from tabname group by col1, col2, col3, col4, col5);

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I dont think the syntax is right.
    Select * from (??);
    Its not returning the right values to select from it.

  4. #4
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    that's an in-line view - select * from (select * from something);

    Code:
    Cat> select * from testdup order by id;
     
            ID       VAL1       VAL2       VAL3
    ---------- ---------- ---------- ----------
             1         20         30         40
             1         20         30         40
             1         20         30         40
             1         20         30         40
             1         20         30         40
             2        200        300        400
     
    6 rows selected.
     
    Cat> select * from (select id, val1, val2, val3, count(*) 
      2  from testdup group by id, val1, val2, val3)
      3  order by id;
     
            ID       VAL1       VAL2       VAL3   COUNT(*)
    ---------- ---------- ---------- ---------- ----------
             1         20         30         40          5
             2        200        300        400          1
     
    2 rows selected.
     
    Cat>

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Indy's idea is in the right direction, he only missed the condition:

    PHP Code:
    Select from (select col1col2col3col4col5count(*) as cnt
    from tabname group by col1
    col2col3col4col5)
    WHERE cnt 1


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Actually, my example doesn't do anything that a straight group by wouldn't give you, without the in-line view

    same as:

    select id, val1, val2, val3, count(*) from testdup
    group by id, val1, val2, val3 order by id;

    Either way - is that whay you need to accomplish?

  7. #7
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Thanks, I knew that in-line was there for a reason...

  8. #8
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Also same as

    select id, val1, val2, val3, count(*)
    from testdup group by id, val1, val2, val3
    having count(*) > 1 order by id;

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    -- Or --
    PHP Code:
    Select  col1col2col3col4col5count(*) 
    from tabname group by col1col2col3col4col5 
    HAVING COUNT
    (*) > 1

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Jul 2005
    Posts
    276
    Now, how do I do this when I have 100 columns?

  11. #11
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    you really have 100 columns that might all be duplicated?

    you can build part of the query by:

    set pagesize 0
    select column_name || ',' from all_tab_columns where owner='MYOWNER'
    and table_name='MYTABLE';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •