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)

AmitDiwan

Updated on 02-Jul-2020 06:53:00

• Related Questions & Answers
• MySQL query to group results by date and display the count of duplicate values?
• How to select multiple max values which would be duplicate values as well in MYSQL?
• Retrieve MIN and MAX date in a single MySQL query from a column with date values
• MySQL query to select records with a particular date?
• MySQL query to select date from timestamp?
• MySQL query to skip the duplicate and select only one from the duplicated values
• MySQL query to select date >= current date - 3 weeks?
• MySQL query to select records with a particular date and time?
• MySQL query to select the values having multiple occurrence and display their count
• MySQL query to select a record with two exact values?
• MySQL query to select closest date from today?
• Get date format DD/MM/YYYY with MySQL Select Query?
• Using GROUP BY and COUNT in a single MySQL query to group duplicate records and display corresponding max value
• MySQL query to get the max value with numeric values in varchar field?
• MySQL query to select column values ending with certain character/number?