每个重复行减少到一行相同的表

Question

Table Name - Enrolments

 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +  unit_id  +  student_number  +  unit_code  +  course_id  +  status_id  + last_modified_date  +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20102   +    SN0001        +  HP-306.12  +   BPL-12    +  WITHDRAWN  +   2/8/2016 15:08     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20102   +    SN0001        +  HP-306.12  +   BMin-12   +  COMPLETED  +   2/8/2016 15:08     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20110  +     SN0002        +  AS-105.11  +   BBS-12    +  WITHDRAWN  +   2/9/2016 12:01     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20110   +    SN0002        +  AS-105.11  +   BMin-13   +  WITHDRAWN  +   2/9/2016 15:13     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+

I've this data as shown in the table-enrolments, there are matching unit_id, student_number

2 conditions i would like to run a sql queries on the table:

a. I would like to remove the 'duplicated' rows and only retain the row that does not have status_id widthdrawn.

b. however, if the 2 'duplicated' rows both have withdrawn status_id and i just want to keep one.

The end result should look like the following table screen shot

Table Name - Newly changed table

 +-----------+------------------+-------------+-------------+-------------+----------------------+
+  unit_id  +  student_number  +  unit_code  +  course_id  +  status_id  +  last_modified_date  +
+-----------+------------------+-------------+-------------+-------------+----------------------+
+   20102   +    SN0001        +  HP-306.12  +   BMin-12   +  COMPLETED  +   2/8/2016 15:08     +
+-----------+------------------+-------------+-------------+-------------+----------------------+
+   20110   +    SN0002        +  AS-105.11  +   BMin-13   +  WITHDRAWN  +   2/9/2016 15:13     +
+-----------+------------------+-------------+-------------+-------------+----------------------+

Anyone can help on this ?

Answer 1

Your solution

Perhaps something like

SELECT * FROM (SELECT * FROM table ORDER BY `status_id`) t1 GROUP BY `unit_id`, `student_number`;

Be warned, I've not tested this query myself so I cannot guarantee it works, report back if it does not and let me know the error ^_^