Monday, September 10, 2018

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.