Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

SQL Select Command to find duplicate values within same file

Hi,

I need a SQL select that will find the duplciate occurance of a field and also list out other fields from this file.    The query below just lists everything.  I need to only list the duplicate occurances.  For example;

NLI_NBR
------------
30011
30011           --> List this one because is it a duplicate
93300
77000
10100          --> List this one because is it a duplicate
10100

SELECT *
FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv]
WHERE EXISTS
(SELECT [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv] WHERE [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] = [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR])
AND
([Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] != ' ')
order by [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR]
0
hojohappy
Asked:
hojohappy
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
The general way to find duplicates is:

select col1, count(*)
from tab1
where condition = value
group by col1
having count(*) > 1

Having said that, I don't know if your select * above is really only returning one row or not.

So maybe something like

select NLI_NBR from
(
SELECT NLI_NBR, count(*) as mycount
FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv]
WHERE EXISTS
(SELECT [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv] WHERE [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] = [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR])
AND
([Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] != ' ')
group by NLI_NBR
having count(*) > 1
) as a
order by [Consolidated US-EN ES CA-FR EN NLI NBRs.csv].[NLI_NBR] 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT *
FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv]
WHERE EXISTS (
   SELECT [NLI_NBR], COUNT(NLI_NBR)
   FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv] 
   GROUP BY [NLI_NBR]
   HAVING COUNT(NLI_NBR) > 1) 

Open in new window

or
SELECT *
FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv]
WHERE [NLI_NBR] IN (
   SELECT [NLI_NBR]
   FROM [Consolidated US-EN ES CA-FR EN NLI NBRs.csv] 
   GROUP BY [NLI_NBR]
   HAVING COUNT(NLI_NBR) > 1) 

Open in new window

btw I have an article SQL Server GROUP BY Solutions where if you scroll two-thirds down to point #5 'Aggregate AND values from a single row that make up the aggregate:  Subquery' it gives a demo on how to pull this off.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now