By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,967 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,967 IT Pros & Developers. It's quick & easy.

Query for duplicates

P: 5
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 ?

Expand|Select|Wrap|Line Numbers
  1. CREATE VIEW test AS
  2. (
  3.     SELECT    field1,
  4.     field2,
  5.     field3,field4,
  6.     field5 ,
  7.     field6,
  8.     id,
  9.     field7,
  10.     COUNT(*) AS REC_COUNT
  11.     FROM     tablename    GROUP BY    
  12.             field1,
  13.             field2,
  14.             field3 ,
  15.             field4,
  16.             field5,
  17.             field6,
  18.             id,
  19.             field7
  20.     HAVING    COUNT(*) > 1
Oct 3 '15 #1
Share this Question
Share on Google+
3 Replies

P: 46
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,, t1.field7
  2.     FROM tablename t1
  3.         INNER JOIN (SELECT field1, field2, field3, field4, field5, field6, field7 , COUNT(*) AS CountOf
  4.                         FROM tablename
  5.                         GROUP BY field1, field2, field3, field4, field5, field6, field7
  6.                         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."
Oct 3 '15 #2

P: 5
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!!!
Oct 4 '15 #3

P: 46
No problem. We've all been there. Glad to help. :)
Oct 5 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.