Select query to display duplicate values with max date

For this, use GROUP BY and HAVING. Let us first create a table −

mysql> create table DemoTable    (    StudentName varchar(100),    DueDate date    ); Query OK, 0 rows affected (0.72 sec)

Example

Insert some records in the table using insert command −

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)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+-------------+------------+ | 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)

Following is the query to display duplicate values with maximum date −

mysql> select tbl.StudentName,max(tbl.DueDate) from DemoTable tbl group by tbl.StudentName having count(*) > 1;

Output

+-------------+------------------+ | 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)