I received quite a few questions after I made comment in my yesterday’s SQL in the Sixty Seconds Video SQL SERVER – TOP and DISTINCT – Epic Confusion, I have made note that ORDER BY is not required when DISTINCT is used. Let us understand the same subject in detail.
The main question which I received is around, why ORDER BY is not required with the DISTINCT. Well, the answer is pretty simple. When you use DISTINCT for any query, the result is always ordered by the columns which you have used in the DISTINCT column.
Well, that’s it. That is the answer.
If you use any other column in the ORDER BY close beside the one used in the SELECT statement, you will get an error in the query.
Additionally, using the ORDER BY actually does not negatively impact the performance or resultset. However, DISTINCT itself orders the column, and adding the order by will be redundant.
Here is a few additional blogs post you may find it relevant to this topic.
- SQL SERVER – Performance Comparison – BETWEEN, IN and Operators
- SQL – Difference between != and <> Operator used for NOT EQUAL TO Operation
- Are Not Equal to Operators Equal to Not In? – SQL in Sixty Seconds #102
- SQL SERVER – Compound Assignment Operators – A Simple Example
- SQL Puzzle – A Quick Fun with BitWise Operator
- Identify Poorly Performing Query Plan Operators – Analyzing SQL Server Query Plans – Part 2
If you like the blog post, you can always follow my YouTube channel where you can see many similar videos.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Good morning, Pinal. I don’t believe that SQL Server guarantees ordering of the results if the DISTINCT keyword is used without a corresponding ORDER BY clause. If the optimizer elects to use a stream aggregate for the DISTINCT, then the results will be ordered. However, if the optimizer uses a hash aggregate for the DISTINCT, the results will be unordered. The presence of a nonclustered index on the table with the leading key column matching the column from the DISTINCT clause will encourage the optimizer to select a stream aggregate, because the values are already sorted.
Hi Bryan, Yes, you are absolutely correct.