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

finding data for duplicates in one field only

P: 62
Hello =

I have a very large table of addresses. I want to find only those records where the company name is the same but the address is different. In other words I want to eliminate records that only have one address for a company name.

I could put the data into Excel and group by company name, then eliminate all records that have a count of only 1. Trouble is, I have almost 150,000 records and this won't fit in an Excel table.

Thank you for your help,
Sandra
Nov 8 '11 #1

✓ answered by NeoPa

Nice answer Sandra, but you can simplify the code somewhat, and/or you could even include it in the main SQL as an embedded sub-query (See Subqueries in SQL) instead.

Expand|Select|Wrap|Line Numbers
  1. SELECT tL.*
  2. FROM   [tLISTING] AS [tL]
  3.        INNER JOIN
  4.        (SELECT   [COMPANY_NM]
  5.         FROM     [tLISTING]
  6.         GROUP BY [COMPANY_NM]
  7.         HAVING   (Count(*) > 1)
  8.        ) AS subQ
  9.   ON   tL.COMPANY_NM = subQ.COMPANY_NM
BTW: Sorting a sub-query is unnecessary and is ignored.

PS. I had to use a different table name as the site treats[LISTING] as recognised tags and loses all spaces prior to it for some reason.

Share this Question
Share on Google+
2 Replies


P: 62
I figured it out.

I created a sub-query called "q_CompanyCount" that had just the "company name" and "count of company name".

Expand|Select|Wrap|Line Numbers
  1. SELECT   LISTING.COMPANY_NM
  2.        , Count(LISTING.COMPANY_NM) AS CountOfCOMPANY_NM
  3. FROM     LISTING
  4. GROUP BY LISTING.COMPANY_NM
  5. ORDER BY Count(LISTING.COMPANY_NM) DESC;
I added this sub-query to the main query, linked by company name, and criteria
Expand|Select|Wrap|Line Numbers
  1. HAVING (((q_CompanyCount.CountOfCOMPANY_NM)>1))
Worked great!
Nov 8 '11 #2

NeoPa
Expert Mod 15k+
P: 31,712
Nice answer Sandra, but you can simplify the code somewhat, and/or you could even include it in the main SQL as an embedded sub-query (See Subqueries in SQL) instead.

Expand|Select|Wrap|Line Numbers
  1. SELECT tL.*
  2. FROM   [tLISTING] AS [tL]
  3.        INNER JOIN
  4.        (SELECT   [COMPANY_NM]
  5.         FROM     [tLISTING]
  6.         GROUP BY [COMPANY_NM]
  7.         HAVING   (Count(*) > 1)
  8.        ) AS subQ
  9.   ON   tL.COMPANY_NM = subQ.COMPANY_NM
BTW: Sorting a sub-query is unnecessary and is ignored.

PS. I had to use a different table name as the site treats[LISTING] as recognised tags and loses all spaces prior to it for some reason.
Nov 11 '11 #3

Post your reply

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