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

Solarwinds

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)

Solarwinds
Previous Post
SQL SERVER – Cumulative Update Package 5 for SQL Server 2005 Service Pack 2
Next Post
SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

Related Posts

29 Comments. Leave new

  • Hi ,

    I want sql querys for following scenarios

    Owner of the company wants to create 5 users,3 in following fashion
    1) 2 -Must have authority of admin
    2) 2- Normal user
    3) 1 – User whose password is blocked for 15 days

    Reply
  • SELECT b.Roles, a.FirstName, a.LastName
    FROM UserNames a
    inner JOIN (SELECT DISTINCT roles from usernames) b ON a.roles = b.roles

    Reply
  • I have 143 million records in my history table. There are lots of duplicate records. If i add distinct it takes longer duration in resulting the unique values. Is there other faster method to extract distinct records from history table.

    Reply
  • Hi Dave,
    I have one query, How to write a query to fetch unique resultset from a table contains ‘N” columns .
    Note: Some rows are duplicated.
    column1 column2 column3 column4 column5 column6 column7 …… ColumnN
    m1
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    m2
    mn rows

    Thanks
    Ramesh

    Reply

Leave a Reply

Menu