Display Highest Amount from Corresponding Duplicate IDs in MySQL

To display highest amount from corresponding duplicate ids, use MAX() along with GROUP BY clause −

mysql> create table DemoTable2003
(
   CustomerId int,
   Amount int
);
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2003 values(101,560);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2003 values(102,1080);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable2003 values(101,570);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable2003 values(102,870);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable2003 values(101,460);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable2003;

This will produce the following output −

+------------+--------+
| CustomerId | Amount |
+------------+--------+
|        101 |    560 |
|        102 |   1080 |
|        101 |    570 |
|        102 |    870 |
|        101 |    460 |
+------------+--------+
5 rows in set (0.00 sec)

Here is the query to display highest amount from corresponding duplicate ids −

mysql> select CustomerId, max(Amount) from DemoTable2003
   group by CustomerId;

This will produce the following output −

+------------+-------------+
| CustomerId | max(Amount) |
+------------+-------------+
|        101 |         570 |
|        102 |        1080 |
+------------+-------------+
2 rows in set (0.00 sec)