What is the difference between GROUP BY and ORDER BY in MySql?

What is the difference between GROUP BY and ORDER BY in MySql?

 

Mostly this question has been asked by the interviewer, even if you have used it many times but at that time generally the guy not satisfied the interviewer by the answer.

 

Please go through the below and give very appropriate answer and increase your chances of selection.

 

GROUP BY

 

GROUP BY is used to make the group-set of records based on the fields of the table or you can say GROUP BY keeps related data items together.

 

GROUP BY clause often used with the aggregate functions (SUM, AVG, MAX) on the columns in a query the other columns should be in group by query, you can use HAVING clause to apply the condition on the group-set that will fetch by the query.

 

Let’s take an example:
Suppose we have a table student as below.

 

tbl_student

——————————————–
Id                     Name
——————————————–
01                     Nirmal
98                     Stephen
65                     Mahesh
65                     Hodge
90                     Pamlea
90                     Paul
90                     Shakira
——————————————-

 

Question 1: Due to some mistake by the programmer, there was some duplicate id for some students. Fetch which id is duplicated and how many times?

Query:

[sourcecode language=”plain”]
SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id
[/sourcecode]

 

Question 2: Fetch which id is duplicated and how many times and the Ids should be greater than 80.

[sourcecode language=”plain”]
SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id
HAVING Id > 80
[/sourcecode]

 

ORDER BY

 

The ORDER BY clause is used to sort the result-set by a specified column either by ASC or DESC.
By default, ORDER BY clause sorts the records in ascending order.

 

ASC = Ascending (shortest first)
Desc = Descending (largest first)

 

[sourcecode language=”plain”]
SELECT Name
FROM tbl_student
ORDER BY Name ASC
[/sourcecode]

 

I think it will make sense?

 

6 thoughts on “What is the difference between GROUP BY and ORDER BY in MySql?”

    1. Oh.. Sandeep
      Thanks, you are right.
      count(Id) > 1 should be there in the query to fetch the duplicates records.

  1. HI

    Hey i don’t want to be hard but i don’t think ever any good interviewer ask such irrelevant question . Difference can be asked where two items have any similarity or any way they can be use same but both have some pros and cons , so based on there pros and cons they can be differentiated .

    Thanks

Comments are closed.