All geek questions in one place

MySQL What is the difference between GROUP BY and DISTINCT?

Possible duplicate:
Is there a difference between Group By and Distinct

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

+4
source share
3 answers

Duplicate

Is there a difference between GROUP BY and DISTINCT

Already discussed here

If you want to listen here

The Well and by group has its own meaning.

Distinct is used to filter unique records from records matching the query criteria.

The group by clause is used to group data on which aggregate functions are run, and the output is returned based on the columns in the group by clause. It has its own limitations, such as all columns that are in the select query, in addition to aggregated functions, should be part of the Group by clause.

Thus, even if you can have the same data returned by separate and group sentences, it is better to use different ones. See the example below.

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

This will make your life easier when there are more columns in the selection list. But at the same time, if you need to display the sum (col10) along with the above columns, you will have to use Group By. In this case, the different ones 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.

+10
source

DISTINCT only works on the entire line. Do not be fooled. SELECT DISTINCT(A), B does something else. This is equivalent to SELECT DISTINCT A, B

On the other hand, GROUP BY creates a group containing all rows that separate each individual value in one column (or in multiple columns or arbitrary expressions). Using GROUP BY , you can use aggregate functions like COUNT and MAX . This is not possible with DISTINCT .

  • If you want all the rows in your result set to be unique and you do not need to aggregate, use DISTINCT .
  • For something more advanced you should use GROUP BY .

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

 SELECT DISTINCT a FROM table1

Results:

  2
 1

But using GROUP BY , the results will be sorted in order:

 SELECT a FROM table1 GROUP BY a

Results:

  1
 2

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

+5
source

Source: https://habr.com/ru/post/1332591/

More articles:


All Articles