CODEWITHSUNDEEP
mysqlsql
user1615666
user1615666

Reputation: 3451

Sql query to clean up junk record

Somehow a table is having junk data, need to clean it up and generate a new table.

I think it should use case or some row_number over, tried a few, failed.

Database is mysql.

original table:

Student Registration    Course
John                    CS
John    2018            
John    2017            
Peter   2019            MATH
Mary    2016            MATH
Mary    2016            CS

The rule is, if we have duplicate records for a student, merge them together, for Registration, take max of year. If no any columns is missing, like Mary. order by Course asc, take first record. so the result will be :

Student Registration    Course
John    2018            CS
Peter   2019            MATH
Mary    2016            CS

Upvotes: 1

Views: 389

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

It looks like you want aggregation:

select student
     , max(registration) as registration
     , min(course) as course
  from original
 group 
    by student;

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

 SELECT Student, MAX(Registration), MAX(Course) 
                              -- or MIN(Course) if you want the first alphabetical
 FROM YourTable
 GROUP BY Student

Upvotes: 2

Related Questions