Find duplicate rows in a table
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)
How to delete the rows which are duplicate (don’t delete both duplicate records).
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0
How to find 6th highest salary
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Saturday, June 12, 2010
Subscribe to:
Post Comments (Atom)
.jpg)
Add on for remove duplicate
ReplyDeleteWITH CTE (EmpName,Score,DuplicateCount)
AS
(
SELECT EmpName,Score,
ROW_NUMBER() OVER(PARTITION BY EmpName,Score ORDER BY EmpName) AS DuplicateCount
FROM Scores
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
-------------------------------------------------
delete from a from
(select EmpName, Score,ROW_NUMBER() over (partition by EmpName, Score order by EmpName, Score) RowNumber
from scores)as a
where a.RowNumber > 1