Abdul Fattah Mohammed Abdul Fattah Mohammed - 1 year ago 68
SQL Question

How to delete records that have same data but different dates

I have a table in a student database that has 49 columns. Every student is assigned a unique ID. There are other two columns for dates. date1 has dates for when the record was inserted into the database. There is another column date2 where it shows the date on which the student registered into a course. also, a student's registration is processed more than once during the semester so there are multiple instances of a student with different dates. I want to remove the duplicates from the table.

Table looks similar to this

id Date1 Date2 TERM_CODE TERM
1 2016-07-06 2011-11-01 201210 2012 Spring
2 2016-07-06 2011-11-17 201210 2012 Spring
4 2016-07-06 2011-11-17 201210 2012 Spring
3 2016-07-06 2011-11-17 201210 2012 Spring
1 2016-07-16 2011-11-09 201210 2012 Spring
2 2016-07-16 2011-11-17 201210 2012 Spring
1 2016-07-16 2011-11-13 201210 2012 Spring
1 2016-07-16 2011-11-17 201210 2012 Spring

I have to select only one record for id 1 and it should be the most recently updated one.

Note: the "term code" and the "term" columns have same values for the record with id 1.

Answer Source

From the description, I think most recently updated means date2. If so, one method uses window functions:

select t.*
from (select t.*,
             row_number() over (partition by id order by date2 desc, date1 desc) as seqnum
      from t
     ) t
where seqnum = 1;

This guarantees exactly one row per id, even if the student has multiple rows with the same date2.

The more traditional SQL method:

select t.*
from t
where t.date2 = (select max(t2.date2) 
                 from t t2 
                 where t2.id = t.id);

would return duplicates, if a student had multiple records with the same date2 value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download