SQL SERVER – DISTINCT Keyword Usage and Common Discussion

Jr. DBA asked me a day ago, how to apply DISTINCT keyword to only first column of SELECT. When asked for additional information about question, he showed me following query.

SELECT Roles, FirstName, LastName
FROM UserNames
He wanted to apply DISTINCT to only Roles and not across FirstName and LastName. When he finished I realize that it is not possible and there is logical error in thinking query like that. I helped him with what he needed however, after he left I realize that answer to his original question was “NO”.

Distinct can not be applied to only few columns it is always applied to whole column. In following exam DISTINCT is applied across Roles, FirstName and LastName.

SELECT DISTINCT Roles, FirstName, LastName
FROM UserNames

Same result as above query can be achieved by using GROUP BY as well.

SELECT Roles, FirstName, LastName
FROM UserNames
GROUP BY Roles, FirstName, LastName

Before I end discussion I would like to add that DISTINCT can be used with aggregated functions and can be applied on only one field in that case. Following example demonstrates that scenario.

SELECT MAX(DISTNCT ID), Roles, FirstName, LastName
FROM UserNames, LastName

This subject is very interesting and I decided to search online if any other writers has explained this in depth. I found following good article written by Jeff Smith. He has explained this example in depth so I have skipped explaining similar example in depth.

Reference : Pinal Dave (https://blog.sqlauthority.com)