Query for duplicates

[pstsqllearner]

I'm using this to create a view for duplicates , but no of records coming out of this view are more than I'm passing in: can anybody help ?

CREATE VIEW test AS
(
    SELECT    field1,
    field2,
    field3,field4,
    field5 ,
    field6,
    id,
    field7,
    COUNT(*) AS REC_COUNT
    FROM     tablename    GROUP BY    
            field1,
            field2,
            field3 ,
            field4,
            field5,
            field6,
            id,
            field7
    HAVING    COUNT(*) > 1

[Vikki McCormick]

Hi, Because 'id' is presumably unique and the row has to be exactly the same in every field, and if you group by all those fields and one of them is different it will register as unique and not a duplicate.

If it is just 'id' that is unique in the field, and you remove 'id' totally you'll get the list. If you need 'id', then you have to set up the select query as a sub-query and re-join that to the main table. There are other ways of doing this using rank() and dense rank(). You are able to number your rows by groups, and then you can pick out the rows you want specifically. I would also try to avoid using count(*) if the tables are large. Hope this helps.

SELECT t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6, t1.id, t1.field7
    FROM tablename t1
        INNER JOIN (SELECT field1, field2, field3, field4, field5, field6, field7 , COUNT(*) AS CountOf
                        FROM tablename
                        GROUP BY field1, field2, field3, field4, field5, field6, field7
                        HAVING COUNT(*)>1 ) as t2 ON t1.field1 =  t2.field1 -- if you have to compare all the fields you can add them in here, "and t1.field2 = t2.field2, etc."
 

[pstsqllearner]

Hi Vikki,

Thanks a TON....I was playing with that query for a while now and your solution really helped, I was not comparing the whole row excluding the unique id....that's why was getting so many duplicates.....thanks Again!!!

[Vikki McCormick]

No problem. We've all been there. Glad to help. :)