इसके लिए GROUP BY और HAVING का उपयोग करें।
उदाहरण
टेबल बनाएं:
mysql> create table DemoTable (
-> StudentName varchar(100),
-> DueDate date
-> );
Query OK, 0 rows affected (0.72 sec)
रिकॉर्ड डालें:
mysql> insert into DemoTable values('John', '2019-01-11');
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable values('Chris', '2019-02-11');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('Chris', '2019-03-11');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('John', '2019-04-11');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Bob', '2019-05-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Bob', '2019-06-11');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Robert', '2019-07-11');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('Robert', '2019-08-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Robert', '2019-09-11');
Query OK, 1 row affected (0.15 sec)
सभी रिकॉर्ड देखें:
mysql> select * from DemoTable;
आउटपुट
+-------------+------------+ | StudentName | DueDate | +-------------+------------+ | John | 2019-01-11 | | Chris | 2019-02-11 | | Chris | 2019-03-11 | | John | 2019-04-11 | | Bob | 2019-05-11 | | Bob | 2019-06-11 | | Robert | 2019-07-11 | | Robert | 2019-08-11 | | Robert | 2019-09-11 | +-------------+------------+ 9 rows in set (0.00 sec)
डुप्लिकेट मानों को अधिकतम तिथि के साथ प्रदर्शित करने की क्वेरी:
mysql> select tbl.StudentName, max(tbl.DueDate) from DemoTable tbl group by tbl.StudentName having count(*) > 1;
आउटपुट
+-------------+------------------+ | StudentName | max(tbl.DueDate) | +-------------+------------------+ | John | 2019-04-11 | | Chris | 2019-03-11 | | Bob | 2019-06-11 | | Robert | 2019-09-11 | +-------------+------------------+ 4 rows in set (0.00 sec)
व्याख्या: GROUP BY नाम से समूह बनाता है, MAX() प्रत्येक समूह की अधिकतम तिथि देता है, HAVING COUNT(*) > 1 केवल डुप्लिकेट वाले नामों को फ़िल्टर करता है।
