How to find duplicate records in SQL?
23,079
Solution 1
Beth is correct - here's my re-write of your query:
SELECT a.studentid, a.measuredate, a.measureid
from [J5C_Measures_Sys] A
GROUP BY a.studentid, a.measuredate, a.measureid
HAVING COUNT(*) > 1
Previously:
SELECT a.studentid, a.measuredate, a.measureid
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID
join sysobjects so on so.name = 'J5C_Measures_Sys'
AND so.type = 'u'
join syscolumns sc on so.id = sc.id
and sc.name = 'score_14'
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
where a.score_14 is not null
AND B.MEASURENAME IS NOT NULL
GROUP BY a.studentid, a.measuredate, a.measureid
HAVING COUNT(*) > 1
Solution 2
you need to take A.score_14 out of your group by clause if you want to count it
![salvationishere](https://i.stack.imgur.com/njBIk.jpg?s=256&g=1)
Author by
salvationishere
Updated on July 25, 2022Comments
-
salvationishere almost 2 years
I am trying to develop a query to insert unique records but am receiving the SQL Server Primary Key error for trying to insert duplicate records. I was able to insert some values with this query but not for this record (score_14).
So now I am trying to find duplicate record with the following query. The challenge is that my PK is based on 3 columns: StudentID, MeasureDate, and MeasureID--all from a different table not mentioned below.
But this only shows me count--instead I want to just return records with count > 1. How do I do that?
select count(a.score_14) as score_count, A.studentid, A.measuredate, B.measurename+' ' +B.LabelName from [J5C_Measures_Sys] A join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID join sysobjects so on so.name = 'J5C_Measures_Sys' join syscolumns sc on so.id = sc.id join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name where so.type = 'u' and sc.name = 'score_14' and a.score_14 is not null AND A.STUDENTID IS NOT NULL AND A.MEASUREDATE IS NOT NULL AND B.MEASURENAME IS NOT NULL --and count(a.score_14)>1 group by a.studentid, a.measuredate, B.measurename, B.LabelName, A.score_14 having count(a.score_14) > 1