Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
dear
 
i have a table which contain duplicate values and i want to view all duplicate values according to
table column 'name' and i want to delete duplicate values from table..
 
table like...
eid     name
1	a
2	b
3	a
4	c
5	d
6	a
7	d
8	a
9	d
10	e
11	a
12	a
13	a
14	e
 
pls help me to resolve above problem..
 
[Update by OP:]
i have use this script but it shows error..
select name,COUNT(*) from t1 having COUNT(*)>=1 group by name
Posted 17-Feb-13 5:59am
Edited 17-Feb-13 6:08am
v2
Comments
Zoltán Zörgő at 17-Feb-13 12:02pm
   
And given this input table, what exactly have you imagined as output?
Are you interested only in the DISTINCT names, or is there any rule which of the eid values should go in the result.
Sergey Alexandrovich Kryukov at 17-Feb-13 13:58pm
   
Please stop posting non-answers as "solution". It can give you abuse reports which eventually may lead to cancellation of your CodeProject membership.
Comment on any posts, reply to available comments, or use "Improve question" (above).
Also, keep in mind that members only get notifications on the post sent in reply to there posts.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

The following statement is not correct.
select name,COUNT(*) from t1 having COUNT(*)>=1 group by name
This one is, since having is a condition that applies to the interim result after aggregation:
select name, COUNT(*) from t1 group by name having COUNT(*)>=1 
But as COUNT(*) won't give 0 in this case, HAVING is no use:
select name, COUNT(*) from t1 group by name
But if you don't need the count, simply use:
select distinct name from t1
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi Mukesh,
 
Check the sample Script,
 

-- Table Creation
IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test(eid  INT IDENTITY(1,1), name VARCHAR(10
0))
INSERT INTO #Test(name)
VALUES('a'),('b'),('b'), ('a'), ('c'), ('d'), ('a'), ('d'), ('a'), ('d'), ('e'), ('a'), ('a'), ('a'), ('e')
-- Actual Data
SELECT Name FROM #Test
 
-- Delete Duplicate Records
DELETE
FROM #Test
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM #Test
GROUP BY Name)
 
-- Required Output
SELECT Name FROM #Test
 

Regards,
Venkatesh.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

For Deleteing the duplicate records from the table you can also use
CREATE TABLE #Test(eid  INT IDENTITY(1,1), name VARCHAR(100))
INSERT INTO #Test(name)
VALUES('a'),('b'),('b'), ('a'), ('c'), ('d'), ('a'), ('d'), ('a'), ('d'), ('e'), ('a'), ('a'), ('a'), ('e')
 
with cte
as
(
SELECT eid,name,ROW_NUMBER() OVER(partition by name order by eid) as rowids FROM #Test
)
delete #Test
from #Test
join cte on #test.eid = cte.eid
where cte.rowids > 1
 
select * From #test
  Permalink  

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

  Print Answers RSS
0 Dnyaneshwar@Pune 604
1 OriginalGriff 394
2 thatraja 370
3 Sergey Alexandrovich Kryukov 334
4 CPallini 219
0 OriginalGriff 697
1 Dnyaneshwar@Pune 604
2 Kornfeld Eliyahu Peter 495
3 thatraja 370
4 Sergey Alexandrovich Kryukov 344


Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 18 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100