Post reply

NOT SELECT DISTINCT

  • May 1, 2003 at 5:27 am

    #80708

    i HAVE A TABLE WITH NO UNIQUE KEY AND HAS DUPLICATE RECORDS IN. i CAN GET THE DISTINCT RECORDS OUT BY 'SELECT DISTINCT * FROM TABLENAME'

    bUT HOW DO YOU GET OUT THE 'NOT' DISTINCT RECORDS

    CAN ANYONE HELP

  • May 1, 2003 at 6:10 am

    #455881

    Try this:

    select a.col1

    from table1 a

    where 1 = (select count(*) from table1 b

    where a.col1 = b.col1)

    This uses a self join to bring back rows where there is only 1 occurrence.

    If you want duplicates, then change the equals to a less than i.e. where 1 is less than the number of rows.

    Jeremy

  • May 1, 2003 at 6:31 am

    #455882

    I have tried this but it needs to be on the whole record. Each column may have numberous records that are the same but the whole record should be unique but are not. I need to find the records that are not.

  • May 1, 2003 at 8:04 am

    #455896

    Is there an ID on each row?

    If so, have you tried:

    SELECT *

    FROM mytable

    WHERE myid NOT IN (SELECT DISTINCT * FROM mytable)

    -SQLBill

  • May 1, 2003 at 12:26 pm

    #455934

    Try This:

    Select <columns to check>

    From <table name>

    Group By <columns to check>

    Having count(*) > 1

    Eric

Viewing 5 posts - 1 through 4 (of 4 total)

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