# question

## Delete specific duplicate records

Can some one please help write a sql to delete the row number (1 and 4) or (4 and 6) from student table by keeping the other records, the idea is to delete where the marks become 0 (+5 -5 =0) Stid Marks s1 5 s2 5 s3 5 s1 -5 s1 6 s1 5

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
If you had a meaningful unique constraint on the table the problem wouldn't have arisen in the first place...
3 Likes 3 ·
·
What should the final result be? Do you just want the total sum of marks per student? What should happen if a single student has 3 records that sum to 0 (like +5, -3, -2)? Also, how far have you gotten so far toward solving this? Can you post whatever you have written?
2 Likes 2 ·
·
As a side note - it is not good practise to be able to have identical rows. i.e your 1st and 6th row are the same. If you had an identity column then this task would be a lot easier
1 Like 1 ·
·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·

·
You should always have a primary key in your table but here's the query that you can run to achieve your goal: DELETE t FROM ( SELECT stid , marks , ROW_NUMBER() OVER ( PARTITION BY Stid, Marks ORDER BY stid ) AS RowNumber FROM TableName ) t WHERE rowNumber > 1 OR ( stid = 's1' AND marks = -5 )

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I understand there should be a primary key, hard coding "stid" or "marks" would not solve the issue, I have millions of records and just trying to eliminate the records where the marks would negate per student (in this case -5 + 5) there could be instance where per student it will be (-3 -2 +5) in this case 3 records should delete. You got the query can we delete without hard coding in the filter.
0 Likes 0 ·
baylar478gmail ·
What do you want to show if a student has two scores: -4 and +5. Do you want both the rows showed?
0 Likes 0 ·
Since you said you want to delete 3 records if the sum of the 3 equals 0, why not just reduce it to 1 record per student with the sum of their scores? SET XACT_ABORT ON; BEGIN TRAN; SELECT stid, marks = SUM(marks) INTO #students FROM students GROUP BY stid ; TRUNCATE students; INSERT students (stid, marks) SELECT stid, marks FROM #students ; COMMIT TRAN;

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I have several other columns in the flat table along with stid,marks like course, faculty around 50 columns, may be I am thinking row-by-row processing.
0 Likes 0 ·
baylar478gmail ·
0 Likes 0 ·
baylar478gmail ·
As a rule, try to avoid row-by-by processing whenever possible. The database engine is much more efficient operating on sets of rows rather than repeating the same operation(s) for each row individually.
0 Likes 0 ·