How can I get the records which are deuplicated in a table?

Question

0.00/5 (No votes)

See more:

Hi,

How can I get the records which are deuplicated in a table?

Eg Table:

VB

ID | Name |Dept |Gender
=======================
1  |ABC   |DPT1 |M
2  |DEF   |DPT2 |M
3  |ABC   |DPT1 |F
4  |DEF   |DPT2 |F
5  |MNO   |DPT3 |M
6  |PQR   |DPT1 |M

Result:

VB

Name
====
ABC
DEF

Thanks,
Janes T

Posted 12-Jan-14 19:52pm

maajanes

Add a Solution

4 solutions

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)

TrushnaK · Answer 1 · 2014-01-12T20:00:00

Solution 1

try this:-

SQL

SELECT name,Count(*) FROM Table_Name
GROUP BY name HAVING COUNT(*) > 1

Posted 12-Jan-14 20:00pm

TrushnaK

Updated 12-Jan-14 20:04pm

v2

Comments

Kornfeld Eliyahu Peter 13-Jan-14 2:04am

Not quit good. as no id, dept and gender are in the group by, it will result in error - you have to remove them...

Maciej Los 13-Jan-14 2:48am

Corresponds to desired output.
+5!
Have a look at my answer.

Karthik_Mahalingam · Answer 2 · 2014-01-12T20:04:00

Solution 2

SQL

SELECT Name,Dept,Gender
FROM tablename
GROUP BY Name,Dept,Gender
HAVING COUNT(*)>1

Posted 12-Jan-14 20:04pm

Karthik_Mahalingam

Updated 12-Jan-14 21:41pm

v2

Comments

Kornfeld Eliyahu Peter 13-Jan-14 2:08am

Not quit good. as no id, dept and gender are in the group by, it will result in error - you have to remove them...

Maciej Los 13-Jan-14 2:46am

What?

Kornfeld Eliyahu Peter 13-Jan-14 2:52am

Column 'table_name.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Kornfeld Eliyahu Peter 13-Jan-14 2:53am

When you have group by you must declare how to group every field in you select list - or aggregate it for every group - so KARTHIK's query will got a syntax error - see before

Maciej Los 13-Jan-14 2:58am

OK, good point!

Kornfeld Eliyahu Peter 13-Jan-14 3:00am

By the way the OP already has his answer - too lazy to say thank you?

Maciej Los 13-Jan-14 3:04am

It happens... The time of reply depends on many things. Many times i've seen reply-comment after few weeks.

Karthik_Mahalingam 13-Jan-14 3:42am

Konrfeld, modified my solution, as per your comments.
i got my mistake..

Maciej Los 13-Jan-14 2:46am

Karthik Bangalore, have a look at your query. It returns nothing. Why? ID's are unique values...
Improve your question to avoid down-voting.

Karthik_Mahalingam 13-Jan-14 3:41am

yes Maciej, i realised my mistake..
modified my solution.

Maciej Los · Answer 3 · 2014-01-12T20:56:00

If you would like to get unigue names, you don't need any aggregate function. Use DISTINCT statement.

SQL

SELECT DISTINCT [name]
FROM TableName

If you would like to get duplicates only, aggregate function is necessary. See solution 1 by TrushnaK.

SQL

SELECT [name]
FROM TableName
GROUP BY [name]
HAVING COUNT([name])>1

But (!)... as you can see, you will get duplicate names, no matter of department and gender. I'm not sure you really want it.

SQL

SELECT [name], [gender], [dept]
FROM TableName
GROUP BY [name], [gender], [dept]
HAVING COUNT([name])>1
ORDER BY [name], [gender], [dept]

Above query returns names with its gender and place of employee hire.

shA.t · Answer 4 · 2015-09-06T01:01:00

You can use `EXISTS` like this:

SQL

SELECT DISTINCT Name
FROM t
WHERE EXISTS(
		SELECT 1 
		FROM t ti 
		WHERE ti.Name = t.Name AND ti.ID <> t.ID)

---

You can use `JOIN` like this:

SQL

SELECT DISTINCT t.Name
FROM t
    JOIN
    t ti ON t.Name = ti.Name AND t.ID <> ti.ID

---

You can use Inner-Select in `WHERE` like this:

SQL

SELECT DISTINCT t.Name
FROM t
WHERE
    1 < (SELECT COUNT(*) FROM t ti WHERE ti.Name = t.Name)