CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2008-r2
Abdul Fattah Mohammed
Abdul Fattah Mohammed

Reputation: 11

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-01   201230     2012 Summer
1       2016-07-06  2011-11-13   201230     2012 Summer
1       2016-07-16  2011-11-03   201260     2012 Fall
1       2016-07-06  2011-11-17   201260     2012 Fall

I have to select all the records for id 1 Where terms are '2012 summer', '2012 fall', '2012 spring' and date1 and date2 are the most recently updated one.

Upvotes: 0

Views: 599

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

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.

Upvotes: 1

Related Questions