Why does the order of column names in GROUP BY clause matter?-CodePudding

I have observed that the order of the rows displayed (for the query) depends on the Order of Column names in GROUP BY CLAUSE

Scenario 1:

SELECT customer_id, staff_id, SUM(amount) 
FROM payment
GROUP BY staff_id, customer_id

Results for Scenario 1:

Scenario 2:

SELECT customer_id, staff_id, SUM(amount) 
FROM payment
GROUP BY customer_id, staff_id

Results for Scenario 2:

CodePudding user response：

The order in which rows are returned as a result of a query where you do not specify an order by criteria is completely arbitrary and at the whim of the database engine.

While you might see ordering of results vary based on numerous factors (the order in which rows are listed in the group by clause for example), without the order by clause the query engine is free to return rows in any order it sees fit.

You are seeing a different set of results purely as a result of varying internal query processing steps, but it's not "determining" the actual ordering.

You may run the same query without an order by 1000 times and get identical results, that is still not a guarantee the 1001th execution will, all other factors equal, be the same.

Any result set, even a simple select * from table will be presented in any order the engine chooses, unless you explicitely define the order.

CodePudding user response：

Are you getting the same answer when you run scenario 1 query multiple times ? I think its all random and has nothing to do with the order of columns mentioned in group by or select clause.