Find Duplicate Entries Column in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have SQL table like this,

COMPANY    | CATEGORY | Session
Wills Corp |   7      |  1
Wills Corp |   7      |  1
Wills Corp |   7      |  2
Stan Co    |   2      |  1
Wills Corp |   5      |  1
Stan Co    |   2      |  1

This section added for clarity by PIEBALDconsult.

Select Distinct COMPANY,CATEGORY,Session from Table

COMPANY    CATEGORY Session
Stan Co    2        1
Wills Corp 5        1
Wills Corp 7        1
Wills Corp 7        2

And there are Many Duplicate Entries in ~~Category and Session~~ COMPANY..I want to find all that have duplicates in ~~either 'Category' or 'Session'~~ COMPANY..i want Output Like this..

COMPANY    | CATEGORY | Session   
Wills Corp |   7      |  1
Wills Corp |   7      |  2
Wills Corp |   5      |  1

I tried the Following Query and it return exact Company name,but i want Category and Session also...

SQL

Select COMPANY,COUNT(*) from (Select Distinct COMPANY,CATEGORY,Session from Table) Tbl group by COMPANY having COUNT(*) > 1

Posted 1-Nov-14 5:33am

MichealRay

Updated 1-Nov-14 7:01am

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 1-Nov-14 11:42am

I'm having trouble trying to match your example data to your description.
Stan Co has duplicates, but you don't want them?
Can you maybe find a way to indicate which source rows match to which destination rows?

MichealRay 1-Nov-14 12:00pm

I want to find duplicate in either Category and Session only...thats y StanCo not came..bcoz Stan Co have same Category and session...

PIEBALDconsult 1-Nov-14 12:21pm

That really doesn't describe it any better.

2 solutions

Solution 1

Try below query:

SQL

SELECT Company, Category, Session, COUNT(*) AS Occurence
FROM TableName
GROUP BY Company, Category, Session
HAVING COUNT(*)>=1

It shows the count of each record.

Second way:

SQL

SELECT *
FROM (
    SELECT Company, Category, Session, ROW_NUMBER() OVER(PARTITION BY Company, Category ORDER BY Session) AS RowNo
    FROM TableName
) AS T
WHERE T.RowNo=1

It shows only first occurence of record ;)

If you would like to delete duplicates, you need to change this part: WHERE condition to RowNo>1

SqlFiddle[^]

Posted 1-Nov-14 6:03am

Maciej Los

Updated 1-Nov-14 6:20am

v3

Comments

MichealRay 1-Nov-14 12:08pm

I tried this Query Already,but it will not return exact output..

Maciej Los 1-Nov-14 12:10pm

No, you did not. There is few differences...

[no name] 1-Nov-14 12:18pm

The first query seems to be so what to be ok. 5 for the query.

Maciej Los 1-Nov-14 12:22pm

Thank you, Bruno ;)

Manas Bhardwaj 2-Nov-14 6:35am

+5!

Maciej Los 2-Nov-14 7:18am

Thank you ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Accepted Answer · 2014-11-01T06:20:00

How about this:

SQL

WITH cte0 AS
(
	SELECT COMPANY , CATEGORY , Session FROM Dupes GROUP BY COMPANY , CATEGORY , Session 
)
, cte1 AS
(
	SELECT COMPANY FROM cte0 GROUP BY COMPANY HAVING COUNT(*) > 1
)
SELECT A.*
FROM cte0 A
INNER JOIN cte1 B
ON A.COMPANY=B.COMPANY

The problem is that you are having trouble describing what you mean by "there are Many Duplicate Entries". You are not actually looking for duplicates in the source table, you are looking for duplicate company values once you have a list of unique records from the source. That is, duplicates in the result of your statement Select Distinct COMPANY,CATEGORY,Session from Table

COMPANY    CATEGORY Session
Stan Co    2        1
Wills Corp 5        1
Wills Corp 7        1
Wills Corp 7        2

I used a Common Table Expression (cte0) to hold that result.
http://technet.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx[^]
(You could use a temporary table if you like.)

The output you specified is any row from that result for which a company name appears more than once.

In cte1 I selected the duplicated Company name(s).

And finally I JOINed the two results to get only those rows from cte0 that have a name that appears more than once.