Tony Stark Tony Stark - 1 month ago 6
SQL Question

confusion over PROCESSING ORDER of "distinct", "top" and "order by" clause

i'm new to sql/sql-server and is having trouble understanding the PROCESSING order of different clauses in a query.

I have a query:

SELECT DISTINCT TOP(10) name
FROM tblEmployees
ORDER BY birthday;


I understand that the actually processing order of the query is:

FROM
SELECT
ORDER BY


However, it seems to me that clauses such as
DISTINCT
and
TOP
is dependent on the result of the
ORDER BY
clause. So if the
SELECT
clause is executed before
ORDER BY
clause, how can
DISTINCT
and
TOP
uses the result from the ORDER BY clause?

Thanks in advance for any help!

Answer

The query is logically processed in below order

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

DISTINCT processed before ORDER BY so DISTINCT is not dependent on ORDER BY.

First duplicates will be removed from result because of DISTINCT then ORDER BY will be applied

Comments