How Can MySQL GROUP BY clause behave like DISTINCT clause?

MySQLMySQLi Database

When we use the GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like the DISTINCT clause. For example, we have the following table −

mysql> Select * from testing;
+------+---------+---------+
| id   | fname   | Lname   |
+------+---------+---------+
|  200 | Raman   | Kumar   |
|  201 | Sahil   | Bhalla  |
|  202 | Gaurav  | NULL    |
|  203 | Aarav   | NULL    |
|  204 | Harshit | Khurana |
|  205 | Rahul   | NULL    |
|  206 | Piyush  | Kohli   |
|  207 | Lovkesh | NULL    |
|  208 | Gaurav  | Kumar   |
|  209 | Raman  | Kumar    |
+------+---------+---------+
10 rows in set (0.00 sec)

By using the DISTINCT clause on column ‘Lname’, MySQL returns the following result set.

mysql> select Distinct LNAME from testing;
+---------+
| LNAME   |
+---------+
| Kumar   |
| Bhalla  |
| NULL    |
| Khurana |
| Kohli   |
+---------+
5 rows in set (0.00 sec)

Now, by using GROUP BY clause as follows, we can get the same result set as we got by using DISTINCT −

mysql> Select LNAME from testing GROUP BY Lname;
+---------+
| LNAME   |
+---------+
| NULL    |
| Bhalla  |
| Khurana |
| Kohli   |
| Kumar   |
+---------+
5 rows in set (0.04 sec)

We can observe a difference between both the result sets returned by MySQL that the result set returns by MySQL query using GROUP BY clause is sorted and in contrast, the result set return by MySQL query using DISTINCT clause is not sorted.

raja
Published on 22-Feb-2018 11:49:16
Advertisements