Solved

SQL Select Command to find duplicate values within same file

Posted on 2013-11-14
3
244 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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

Expert Comment

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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Sum Over Multiple Tables 20 35
Can I skip a node in XML? 9 36
Want an individual results display div 8 49
Using datetime as triggers 2 27
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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