Syntax error How to implement MAX(distinct…) in MySQL and what is the difference without using DISTINCT?

How to implement MAX(distinct…) in MySQL and what is the difference without using DISTINCT?

Let us see the first syntax, which uses DISTINCT in MAX() −

select max(DISTINCT yourColumnName) from yourTableName;

The second syntax is as follows. It isn’t using DISTINCT −

select max( yourColumnName) from yourTableName;

NOTE − Both the above queries give the same result with or without a DISTINCT keyword. MySQL internally converts MAX(yourColumnName) to DISTINCT keyword.

Let us now see an example and create a table −

mysql> create table DemoTable
(
   Number int
);
Query OK, 0 rows affected (1.50 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(80);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(78);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(68);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values(88);
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+
| Number |
+--------+
|     80 |
|     88 |
|     78 |
|     88 |
|     68 |
|     88 |
+--------+
6 rows in set (0.00 sec)

CASE 1 − Following is the query to implement MAX(DISTINCT..) −

mysql> select max(DISTINCT Number) from DemoTable;

This will produce the following output −

+----------------------+
| max(DISTINCT Number) |
+----------------------+
|                   88 |
+----------------------+
1 row in set (0.00 sec)

CASE 2 − Following is the query to implement MAX() without DISTINCT −

mysql> select max(Number) from DemoTable;

This will produce the following output −

+-------------+
| max(Number) |
+-------------+
|          88 |
+-------------+
1 row in set (0.07 sec)

As you can see above, both of them give the same result.

Updated on: 2019-10-03T07:47:26+05:30

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements