Will-I-am-davidon Will-I-am-davidon - 5 months ago 16
MySQL Question

MySQL how to select records of a field doesn't have all required values

In MySQL, how to select the records with a specific field doesn't cover all required values?

for example, in the following records, some students finished continuous numbered assignments, like s1 finished from 1 to 5, s4 finished from 1 to 7, but s1 and s4 had different MAX NUMBER of assignments. While some students finished NON-CONTINUOUS numbered assignments, e.g. s2 and s3. how to select the students who have finished CONTINUOUS NUMBERED assignments, regardless the max number?

id student_id assignment_done


1 s1 1


2 s1 2


3 s1 3


4 s1 4


5 s1 5


6 s2 2


7 s2 4


8 s3 1


9 s3 5


10 s4 1


11 s4 2


12 s4 3


13 s4 4


14 s4 5


15 s4 6


16 s4 7

Answer
Select student_id from STUDENT group by student_id having count( assignment_done) = max(assignment_done);
+------------+
| student_id |
+------------+
| s1         |
| s4         |
+------------+
2 rows in set (0.00 sec)
Comments