Logical Processing places DISTINCT before TOP in a SELECT statement. Questions after my video prompted this explanation.
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
DECLARE @T table (ID int);
INSERT @T VALUES (2),(1),(1),(3);
SELECT DISTINCT TOP (2) ID FROM @T ORDER BY ID;
The example returns the lowest two distinct IDs, 1 and 2. It does not take two source rows before deduplicating them. ORDER BY determines which distinct values TOP retains. Without ordering, the selected subset is unpredictable.
The logical model explains result semantics and name binding. The optimizer can transform physical work while preserving required behavior. SELECT aliases are generally unavailable to WHERE because WHERE logically precedes SELECT.
The video and linked example cover the broader TOP discussion. Study physical execution in the actual plan. Don’t interpret the logical list as a mandatory runtime schedule.
Related reading
Original video
Logical processing order is not a physical operator schedule, it is a model of required query semantics.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
