Does ms access group by order the results?

Question

I need to run a query that groups the result and orders it. When I used the following query I noticed that the results were ordered by the field name:

SELECT name, count(name) 
FROM contacts 
GROUP BY name 
HAVING count(name)>1 

Originally I planed on using the following query:

SELECT name, count(name) 
FROM contacts 
GROUP BY name 
HAVING count(name)>1
ORDER BY name

I'm worried that order by significantly slows the running time. Can I depend on ms-access to always order by the field I am grouping by, and eliminate the order by?

EDIT: I tried grouping different fields in other tables and it was always ordered by the grouped field.

I have found answers to this question to other SQL DBMSs, but not access.

Answer 1

How GROUP BY and ORDER BY work in general

Databases usually choose between sorting and hashing when creating groups for GROUP BY or DISTINCT operations. If they do choose sorting, you might get lucky and the sorting is stable between the application of GROUP BY and the actual result set consumption. But at some later point, this may break as the database might suddenly prefer an order-less hashing algorithm to produce groups.

In no database, you should ever rely on any implicit ordering behaviour. You should always use explicit ORDER BY. If the database is sophisticated enough, adding an explicit ORDER BY clause will hint that sorting is more optimal for the grouping operation as well, as the sorting can then be re-used in the query execution pipeline.

How this translates to your observation

I tried grouping different fields in other tables and it was always ordered by the grouped field.

Have you exhaustively tried all possible queries that could ever be expressed? I.e. have you tried:

• JOIN
• OUTER JOIN
• semi-JOIN (using EXISTS or IN)
• anti-JOIN (using NOT EXISTS or NOT IN)
• filtering
• grouping by many many columns
• DISTINCT + GROUP BY (this will certainly break your ordering)
• UNION or UNION ALL (which defeats this argument anyway)

I bet you haven't. And even if you tried all of the above, can you be sure there isn't a very peculiar configuration where the above breaks, just because you've observed the behaviour in some (many) experiments?

You cannot.

MS Access specific behaviour

As far as MS Access is concerned, consider the documentation on ORDER BY

Remarks
ORDER BY is optional. However, if you want your data displayed in sorted order, then you must use ORDER BY.

Notice the wording. "You must use ORDER BY". So, MS Acces is no different from other databases.

The answer

So your question about performance is going in the wrong direction. You cannot sacrifice correctness for performance in this case. Better tackle performance by using indexes.