- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How Can MySQL GROUP BY clause behave like DISTINCT clause?
152 Lectures 16 hours
87 Lectures 5.5 hours
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.
- Can we use MySQL GROUP BY clause with multiple columns like MySQL DISTINCT clause is used?
- How Can we use MySQL DISTINCT clause with WHERE and LIMIT clause?
- How can we create a MySQL view with GROUP BY clause?
- How can group functions be used in ORDER BY clause?
- How to order or choose rows in MySQL GROUP BY clause?
- How to use user variables in MySQL LIKE clause?
- How to use MySQL DISTINCT clause on multiple columns?
- Using LIKE clause twice in a MySQL query
- How will GROUP BY clause perform without an aggregate function?
- How can we create the MySQL view with ORDER BY clause?
- MySQL GROUP BY with WHERE clause and condition count greater than 1?
- What is the benefit of using MySQL SUM() function with GROUP BY clause?
- What is the significance of using multiple columns in MySQL GROUP BY clause?
- Why should we not use group functions with non-group fields without GROUP BY clause in MySQL SELECT query?
- How can we use MySQL SELECT without FROM clause?