Does "group by" automatically guarantee "order"?

Question

Does the "group by" clause automatically guarantee that the results will be sorted by this key? In other words, is it enough to write:

select * from table group by a, b, c 

or need to write

 select * from table group by a, b, c order by a, b, c 

I know for example. in MySQL I don’t need this, but I would like to know if I can rely on it in comparison with SQL implementations. Guaranteed?

Answer 1

group by does not organize data. The database is designed for quick data collection and, if necessary, sorts them.

So add order by if you need a guaranteed order.

Answer 2

That definitely does not mean. I experienced this when one of my queries suddenly began to return unordered results, as the data in the table grows.

Answer 3

I tried. Adventureworks db of Msdn.

 select HireDate, min(JobTitle) from AdventureWorks2016CTP3.HumanResources.Employee group by HireDate 

Resuts:

2009-01-10Production Technician - WC40

2009-01-11Application Specialist

2009-01-12Assistant for CFO

2009-01-13Production Technician - WC50 <

It returns the sorted data of the tenant, but you do not rely on GROUP BY for SORT under any circumstances.

eg; indexes can modify this sorted data.

I added the following index (hiredate, jobtitle)

 CREATE NONCLUSTERED INDEX NonClusturedIndex_Jobtitle_hireddate ON [HumanResources].[Employee] ( [JobTitle] ASC, [HireDate] ASC ) 

The result will change with the same select query;

2006-06-30 Production Technician - WC60

2007-01-26 Marketing Assistant

2007-11-11 Engineering Manager

2007-12-05 Chief designer of tools

2007-12-11 Tool Designer

2007-12-20 Marketing Manager

2007-12-26 Production Manager - WC60

You can download Adventureworks2016 at the following address

https://www.microsoft.com/en-us/download/details.aspx?id=49502

Answer 4

It depends on the number of entries. When there are fewer entries, Grouping is sorted automatically. When there are more records (more than 15), you need to add the order order by