MySQL SELECT DISTINCT สำหรับหลายคอลัมน์

ตัวอย่างการใช้ DISTINCT กับหลายคอลัมน์ เริ่มจากสร้างตาราง:

mysql> create table selectDistinctDemo
    -> (
    -> InstructorId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> StudentId int,
    -> TechnicalSubject varchar(100)
    -> );
Query OK, 0 rows affected (0.50 sec)

เพิ่มข้อมูล:

mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'Java');
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'MongoDB');
Query OK, 1 row affected (0.16 sec)
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(121,'MySQL');
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(298,'Python');
Query OK, 1 row affected (0.11 sec)
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(298,'SQL Server');
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectDistinctDemo(StudentId,TechnicalSubject) values(397,'C#');
Query OK, 1 row affected (0.13 sec)

แสดงข้อมูลทั้งหมด:

mysql> select * from selectDistinctDemo;

+--------------+-----------+------------------+
| InstructorId | StudentId | TechnicalSubject |
+--------------+-----------+------------------+
| 1            | 121       | Java             |
| 2            | 121       | MongoDB          |
| 3            | 121       | MySQL            |
| 4            | 298       | Python           |
| 5            | 298       | SQL Server       |
| 6            | 397       | C#               |
+--------------+-----------+------------------+
6 rows in set (0.00 sec)

คำสั่ง SELECT DISTINCT หลายคอลัมน์ (ดึงรายวิชาสุดท้ายของแต่ละนักศึกษา):

mysql> select InstructorId,StudentId,TechnicalSubject from selectDistinctDemo
    -> where InstructorId IN
    -> (
    -> select max(InstructorId) from selectDistinctDemo
    -> group by StudentId
    -> )
    -> order by InstructorId desc;

+--------------+-----------+------------------+
| InstructorId | StudentId | TechnicalSubject |
+--------------+-----------+------------------+
| 6            | 397       | C#               |
| 5            | 298       | SQL Server       |
| 3            | 121       | MySQL            |
+--------------+-----------+------------------+
3 rows in set (0.10 sec)