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

Filter records based on shared id

P: 22
I am reviewing a set of 100,000 records looking for anomalies where there are two or more sharing the same id (not pk). How can I filter out the single records and save myself a lot of button pressing?
Jun 6 '18 #1

✓ answered by PhilOfWalton

Firstly create a group by query something like
Expand|Select|Wrap|Line Numbers
  1. SELECT MyTable.ID, Count(MyTable.ID) AS CountOfID
  2. FROM MyTable
  3. GROUP BY MyTable.ID
  4. HAVING (Count(MyTable.ID))>1;
  5.  
This will find all your duplicate (triplicate etc) IDs. Let's call this Query1.

Then a second query like
Expand|Select|Wrap|Line Numbers
  1. SELECT MyTable.*
  2. FROM MyTable INNER JOIN Query1 ON MyTable.ID = Query1.ID;
  3.  
This should give the complete record of the duplicates

Phil

Share this Question
Share on Google+
1 Reply


PhilOfWalton
Expert 100+
P: 1,430
Firstly create a group by query something like
Expand|Select|Wrap|Line Numbers
  1. SELECT MyTable.ID, Count(MyTable.ID) AS CountOfID
  2. FROM MyTable
  3. GROUP BY MyTable.ID
  4. HAVING (Count(MyTable.ID))>1;
  5.  
This will find all your duplicate (triplicate etc) IDs. Let's call this Query1.

Then a second query like
Expand|Select|Wrap|Line Numbers
  1. SELECT MyTable.*
  2. FROM MyTable INNER JOIN Query1 ON MyTable.ID = Query1.ID;
  3.  
This should give the complete record of the duplicates

Phil
Jun 6 '18 #2

Post your reply

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