Does the order of the columns in the group match the article?

Question

If I have two columns, one with very high power and one with very low power (a unique number of values), does it matter in which order I group?

Here is an example:

select dimensionName, dimensionCategory, sum(someFact) from SomeFact f join SomeDim d on f.dimensionKey = d.dimensionKey group by d.dimensionName, -- large number of unique values d.dimensionCategory -- small number of unique values 

Are there any situations in which this matters?

Answer 1

No, the order does not matter for the GROUP BY clause.

MySQL and SQLite are the only databases that I know of that allow you to select columns that are omitted from the group (non-standard, non-portable), but the order also does not matter.

Answer 2

SQL is declarative.

In this case, you told the optimizer how you want to group the data, and it finds out how to do it.

It will not evaluate row by row (procedural) and look at one column first

The basic order of column columns is relevant for indexes. col1, col2 does not match col2, col1 . For everyone.

Answer 3

There is an outdated, non-standard Microsoft SQL Server feature called ROLLUP. ROLLUP is an extension of the GROUP BY syntax, and when used, the column order of GROUP BY determines which columns should be grouped as a result. However, ROLLUP is not recommended. A standard alternative to SQL is to use the groupings supported by SQL Server 2008 and later.

Answer 4

Since it was not mentioned here. The above answers are correct, that is, the order of the columns after the "group by" clause will not affect the correctness of the request (ie, Amounts).

However, the order of the resulting rows will vary depending on the order of the columns specified after the group by clause. For example, consider table A with the following lines:

 Col1 Col2 Col3 1 xyz 100 2 abc 200 3 xyz 300 3 xyz 400 

SELECT *, SUM(Col3) FROM A GROUP BY Col2, Col1 will retrieve rows sorted by Col2 in ascending order.

 Col1 Col2 Col3 sum(Col3) 2 abc 200 200 1 xyz 100 100 3 xyz 300 700 

Now change the order of the columns in the group to Col1, Col2 . The resulting rows are ordered using Col1 .

i.e. select *, sum(Col3) from A group by Col1, Col2

 Col1 Col2 Col3 sum(Col3) 1 xyz 100 100 2 abc 200 200 3 xyz 300 700 

Note. The summation sum (i.e. the correctness of the request) remains exactly the same.

Answer 5

If I have two columns, one with very high power and one with very low power (a unique number of values), does it matter in which order I group?

Request-1

 SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt FROM tbl_product_spec GROUP BY spec_id, catid, spec_display_value ; 

Request 2

 SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt FROM tbl_product_spec FORCE INDEX(idx_comp_spec_cnt) GROUP BY catid, spec_id,spec_display_value; 

Both are equal, the order does not work in group by.