9

Suppose I have a simple query

SELECT col1, col2, COUNT(col3)  
FROM tbl_name    
WHERE condition    
GROUP BY col1, col2    
ORDER BY col1 ;

My understanding is group by clause seems to already have a sorting functionality. DB engine would sort col1 then col2 if adjacent rows of col1 happen to have same values. As least that is my experience with sql server.

If that is the case, order by clause here isn't really necessary?

2
  • 5
    Just add an index on (col2, col1) INCLUDE (col3) and try your query, with and without ORDER BY. Commented Jul 4, 2016 at 6:14
  • If you need to keep order use this: SELECT c.inputFile, MAX(c.TimestampUpdate) AS LogDate FROM YOUR_TABLE c GROUP BY c.inputFile ORDER BY LogDate DESC Commented Oct 8, 2019 at 19:35

3 Answers 3

16

My understanding is group by clause seems to already have a sorting functionality.

There are two basic approaches that SQL Server can use. A stream aggregate requires the data to be sorted by the group by keys. This can be either supplied by an index or might need an explicit sort. A stream aggregate is order preserving in that the rows output from that operator are in the same order as the input. This does not imply any guarantee about the eventual output from the query as a whole however. You only get that if you add an ORDER BY.

For a GROUP BY col1, col2 the input stream can be ordered by either col1, col2 or col2, col1 to be acceptable for the stream aggregate in this case. The addition of an index might change the decision of the optimiser as to which one to use.

The other basic approach is that of a hash aggregate. In which the grouping keys are hashed. This is not at all order preserving and will likely output rows in seemingly random orders.

Additionally for parallel plans there might be hybrid approaches, e.g. Each thread could have a local stream aggregate with the thread results then aggregated at global level with a hash aggregate.

Adding ORDER BY means that SQL Server will ensure that the rows are delivered in the desired order. If you are currently observing an output order of col1, col2 then likely you are getting a stream aggregate ordered by those two columns. The addition of an explicit order by won't change the execution plan to add any additional sort as SQL Server will recognize that the output of this aggregate is already in the desired order.

0
14

Yes, the ORDER BY is necessary, unless you don't actually care about the order. Just because you observe some type of sorting does not make it guaranteed. Basically, if you leave out the ORDER BY clause, you're telling SQL Server that you don't care about the order, and that it is free to return the data in whatever order it deems most efficient. And it will; see #3 here :

If you want that order, what harm is there in actually typing out the ORDER BY you want? This documents the query, ensures you always get the results you want, and I'm not sure what leaving it out would buy you. 13 saved keystrokes? One fewer line of code? Why do people want to go out of their way to make their code ambiguous and unreliable?

0
1

The ORDER BY clause is not strictly necessary. It’s up to you to decide whether you want the result set ordered. However, given the whole point of GROUP BY, the following is recommended:

  • You probably want the data displayed in the same order as per the GROUP BY clause, to make the grouping more apparent.
  • You probably want the GROUP BY columns in the SELECT clause, though not necessarily in the same sequence.

While a product such as MS Access apparently does order the results automatically, this is not to be expected in a more serious database such as SQL Server.

If you don’t use ORDER BY, the results are not guaranteed to be in any particular order. As part of the process, the data may be sorted internally, so the result set may appear in order, but that’s not reliable.

I often find that the left over order is by the last column in the list, so it makes sense to take control with the ORDER BY clause and do it properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.