how to remove non-duplicated row
I have a person table, I want to remove all the entry with count=1
how to do that?
select grp, COUNT(*) as s from hpmg_dup_person
group by grp
order by show to do that?
ASKER
Msg 4403, Level 16, State 1, Line 2
Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--So effectively you wanted to to have records where you have more than 1 record per grp. In this case you can use row_number to delete the records.
--SOLUTION 1
--SOLUTION 2
The above code will delete records where count per grp is 1. !!
O/p
/*------------------------
DELETE b
FROM
(
SELECT * FROM
(
select grp, COUNT(*) as s from hpmg_dup_person
group by grp
)u
WHERE s = 1
)k INNER JOIN hpmg_dup_person b ON k.grp = b.grp
------------------------*/
(4 row(s) affected)
--
CREATE TABLE hpmg_dup_person
(
grp INT
,a INT
)
GO
INSERT INTO hpmg_dup_person
VALUES
(1,100),
(1,101),
(2,102),
(2,103),
(3,104),
(4,105),
(5,106),
(6,107)
GO
----SOLUTION 1
--
DELETE a
FROM (SELECT *, COUNT(*) OVER (PARTITION BY grp) cnt FROM hpmg_dup_person)a WHERE cnt = 1
----SOLUTION 2
--
DELETE b FROM
(
SELECT * FROM
(
select grp, COUNT(*) as s from hpmg_dup_person
group by grp
)u
WHERE s = 1
)k INNER JOIN hpmg_dup_person b ON k.grp = b.grp
--The above code will delete records where count per grp is 1. !!
O/p
/*------------------------
DELETE b
FROM
(
SELECT * FROM
(
select grp, COUNT(*) as s from hpmg_dup_person
group by grp
)u
WHERE s = 1
)k INNER JOIN hpmg_dup_person b ON k.grp = b.grp
------------------------*/
(4 row(s) affected)
Hi wasabi3689,
A feedback will be appreciated.
Regards
Pawan
A feedback will be appreciated.
Regards
Pawan
Open in new window
Deleting duplicates is covered here --> SQL Server Delete Duplicate Rows. I'll have to add a blurb to this to cover deleting non-duplicates per your question.