how to find records with dupliacte filed value

Question

0.00/5 (No votes)

See more:

i have a table which may contain few duplicate values like

id.................fullname.......... username....... dept
1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD
3.................alex gill..........alexis...............xyz
4.................harry dsdsd............devil...............abc
5................ harry kath............kathhh.........gggg

now i want to fetch only those records with same full name.

1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD.

and i want to concat user name with full name for the records with same full name
like

wayne roo[rooney]
wayne roo[wayne]
alex gill
harry dsdsd
harry kath

how do i do this.. pls help...
thanks in advance

Posted 3-Aug-11 1:23am

greatreddevil

Updated 3-Aug-11 1:25am

v2

Add a Solution

3 solutions

Solution 5

try something like

Select fullname, username, dept from tablename tn
Join tablename on tn.fullname = tablename.fullname and tn.id <> tablename.id

Posted 3-Aug-11 2:47am

Paul E Davies

Comments

UJimbo 3-Aug-11 9:13am

Your T-SQL is only showing the duplicates, ignoring the records appearing one time.

Paul E Davies 3-Aug-11 9:40am

I thought that's what the OP asked for

now i want to fetch only those records with same full name.

1.................wayne roo.........rooney...........OT
2.................wayne roo.........wayne............RnD.

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)

UJimbo · Accepted Answer · 2011-08-03T02:55:00

Solution 7

SELECT fullname as fname INTO #temp1 FROM TestTable
GROUP BY 
    fullname
HAVING 
    (COUNT(fullname) > 1); 

SELECT (fullname+ '[' + username + ']') from TestTable thetest
right JOIN #temp1 T on thetest.fullname = T.fname

UNION

SELECT fullname
FROM TestTable
GROUP BY fullname
HAVING ( COUNT(fullname) = 1 );

drop TABLE #temp1;

Posted 3-Aug-11 2:55am

UJimbo

Comments

greatreddevil 4-Aug-11 0:00am

it works thanks... :)

UJimbo 4-Aug-11 2:46am

You are welcome. Take care :)

Roden You · Accepted Answer · 2011-08-03T02:48:00

Solution 6

select fullname||username from table where fullname in(
SELECT fullname FROM Table
group by fullname having count(*)>1)
union
select fullname from table 
group by fullname having count(*)=1

Try it !

Posted 3-Aug-11 2:48am

Roden You

Updated 3-Aug-11 2:49am

v2

Comments

greatreddevil 4-Aug-11 0:00am

this works.... thanks a lot :)