Classic Mistake

Today I was working on an ETL feed that parsed data elements from a single column of a single string value. The table held a log from an enterprise build tool, and we wanted to analyze the logs to find patterns of unusually slow builds.

https://dba.stackexchange.com/questions/218073/the-disappearing-act-of-the-invalid-length-parameter-passed-to-the-left-or-subs

DISTINCT with TOP n

I came across an uncommon query need to use DISTINCT and TOP n in the same query, and it made me realize that I wasn't sure how they would interact. Which would take precedence? I decided to create a simple example to help understand this.

DECLARE @x TABLE (gender char(1))

INSERT @x VALUES ('M')
INSERT @x VALUES ('M')
INSERT @x VALUES ('F')
INSERT @x VALUES ('F')

SELECT DISTINCT TOP 2 gender FROM @x ORDER BY gender

If the TOP 2 took precedence and acted first, it would take the top 2 values of gender, 'F' and 'F', and then remove duplicates in the DISTINCT clause, resulting in one row of 'F'.

If the DISTINCT acted first, it would have the preliminary result of two rows, 'F' and 'M', and then the TOP 2 would return just that. 

 

When we run this code, we get two rows, 'F' and 'M', showing that the DISTINCT takes precedence over the TOP 2.