SQL SERVER – Distinct and ORDER BY

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.

SQL SERVER - Distinct and ORDER BY distinct-800x598

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.

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)

Execution Plan, SQL Operator, SQL Order By, SQL Scripts, SQL Server
Previous Post
Next Post
SQL SERVER Management Studio – Word Wrap

Related Posts

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.


Leave a Reply