Saturday, August 5, 2017

Advanced SQL: Find groups of students who take the same courses

How to group students who take the same courses using SQL? The trick is to assign a unique value to the combination of courses taken by a student, and then group students by their assigned values. There are 2 options for the assignment.

  1. Assign every course a unique bit. For example, course A = 1 (0001), course B = 2 (0010), course C = 4 (0100), etc. Then, for all possible combinations of courses, their sum of the bits are unique.
  2. Sort and concatenate course IDs.
Option 2's string concatenation is not SQL standard. Different RDBMS has different syntax. Option 1, on the other hand, can be implemented in standard SQL. Below is an example for option 1.

No comments: