MySQL - What is the difference between GROUP BY and DISTINCT? [duplicate]

Question

Possible Duplicate:
Is there any difference between Group By and Distinct

What's the difference between GROUP BY and DISTINCT in a MySQL query?

Answer 1

Duplicate of

Is there any difference between GROUP BY and DISTINCT

It is already discussed here

If still want to listen here

Well group by and distinct has its own use.

Distinct is used to filter unique records out of the records that satisfy the query criteria.

Group by clause is used to group the data upon which the aggregate functions are fired and the output is returned based on the columns in the group by clause. It has its own limitations such as all the columns that are in the select query apart from the aggregate functions have to be the part of the Group by clause.

So even though you can have the same data returned by distinct and group by clause its better to use distinct. See the below example

select col1,col2,col3,col4,col5,col6,col7,col8,col9 from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9

can be written as

select distinct col1,col2,col3,col4,col5,col6,col7,col8,col9 from table

It makes you life easier when you have more columns in the select list. But at the same time if you need to display sum(col10) along with the above columns than you will have to use Group By. In that case distinct will not work.

eg

select col1,col2,col3,col4,col5,col6,col7,col8,col9,sum(col10) from table group by col1,col2,col3,col4,col5,col6,col7,col8,col9

Hope this helps.

Answer 2

DISTINCT works only on the entire row. Don't be mislead into thinking SELECT DISTINCT(A), B does something different. This is equivalent to SELECT DISTINCT A, B.

On the other hand GROUP BY creates a group containing all the rows that share each distinct value in a single column (or in a number of columns, or arbitrary expressions). Using GROUP BY you can use aggregate functions such as COUNT and MAX. This is not possible with DISTINCT.

• If you want to ensure that all rows in your result set are unique and you do not need to aggregate then use DISTINCT.
• For anything more advanced you should use GROUP BY.

---

Another difference that applies only to MySQL is that GROUP BY also implies an ORDER BY unless you specify otherwise. Here's what can happen if you use DISTINCT:

SELECT DISTINCT a FROM table1

Results:

2
1

But using GROUP BY the results will come in sorted order:

SELECT a FROM table1 GROUP BY a

Results:

1
2

As a result of the lack of sorting using DISTINCT is faster in the case where you can use either. Note: if you don't need the sorting with GROUP BY you can add ORDER BY NULL to improve performance.