Syntax error Display highest amount from corresponding duplicate ids in MySQL

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)
Updated on: 2020-01-02T05:46:16+05:30

874 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements