?
Solved

SQL Select Command to find duplicate values within same file

Posted on 2013-11-14
3
Medium Priority
?
246 Views
Last Modified: 2013-12-12
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
Comment
Question by:hojohappy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39649105
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39649118
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39713953
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question