DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Duplicated rows

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    If table has no PK, and no Unique key

    How else can I found out if table has duplicated rows.
    Example

    Table = table1
    with
    col1 = Varchar
    col2 = Number
    col3 = Date
    col4 = Varchar


    Thanks in Advance

  2. #2
    Join Date
    Nov 2000
    Posts
    224
    based on column value using group by clause

    select lastname,firstname
    from employee
    group by lastname,firstname
    having count(*) > 1;

  3. #3
    Join Date
    Mar 2002
    Posts
    60
    u can also get duplicate rows by using max(rowid)

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    sumi_ocp What do you mean and how ?

  5. #5
    Join Date
    Mar 2002
    Posts
    16
    His mean u can get distinct rows by using rowid as follows.

    select * from emp where rowid in (
    select max(rowid) from emp group by empno)


    Like this u can get distinct rows or u can deleted duplicate rows by keeping one row.


    Cheers,
    Ganesh.G.
    Ganesh.G

  6. #6
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Here is sql which will display how many duplicate records are there for the table TABLE1

    Select col1, col2, col3, col4,count(*) NO_OF_DUPLICATES
    from table1
    group by col1,col2,col3,col4
    having count(*) > 1;

    Regards
    Santosh

Posting Permissions

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


Click Here to Expand Forum to Full Width