MySQL-এ একাধিক কলামের জন্য SELECT DISTINCT কীভাবে ব্যবহার করবেন?

MySQL-এ একাধিক কলামের জন্য SELECT 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)

একাধিক কলামের জন্য DISTINCT ব্যবহার করে প্রতিটি StudentId-এর সর্বোচ্চ InstructorId বের করার কোয়েরি:

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)