Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

By The Way ... DISTINCT is not a function ...

Have you ever seen (or written) code like this:

select distinct(employeeID), salary
from salaryhist

That compiles and executes without returning any errors.  I've seen that attempted many times over the years, and of course people think DISTINCT is "broken" and "not working" because they see multiple rows for each employeeID.  "But I asked for only distinct employeeIDs!" they say.

Well, the DISTINCT has nothing to do with the EmployeeID column; it is not a function that accepts arguments!  It is just a tag that you can put after the word SELECT to indicate that you want only distinct combinations of all columns in the result set returned.

That syntax is accepted because (employeeID) is just an expression, a reference to a column, which happens to be surrounded by parenthesis.  For example, you could write:

select distinct (employeeID), (salary)
from salaryhist

or:

select (employeeID), (salary)
from salaryhist

or even:

select distinct ((employeeID)), ((salary))
from salaryhist

Nothing is indicating that DISTINCT should be "operating" on the employeeID column; it is just a column reference in the SELECT clause that happens to be surrounded by parenthesis. 

So, remember:
  • DISTINCT always operates on all columns in the final result
  • DISTINCT is not a function that accepts a column as an argument
When you do want to return multiple columns in your result, but only have them be distinct for a subset of those columns, you would use GROUP BY.  And, of course, you must specify how you'd like to summarize all non distinct/grouped columns for any others you'd like to return:

select employeeID, max(salary) as MaxSalary
from salaryhist
group by employeeID

Notice that now we are getting distinct EmployeeID values, and the max salary per EmployeeID.  This will truly return exactly one row per EmployeeID, unlike the initial DISTINCT example.  And, in this case, MAX() is indeed a function that accepts and acts upon an argument -- unlike DISTINCT!

see also:

Print | posted on Friday, October 12, 2007 10:45 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: By The Way ... DISTINCT is not a function ...

Ugh, yes--seen this several times.

Even worse, something like:

SELECT DISTINCT(EmployeeId), MAX(Salary)
FROM SalaryHist
GROUP BY EmployeeId

10/12/2007 11:18 AM | Adam Machanic
Gravatar

# re: By The Way ... DISTINCT is not a function ...

... except when DISTINCT is inside an aggregate function:

SELECT COUNT(DISTINCT n), COUNT(n), SUM(DISTINCT n), SUM(n)
FROM (
SELECT 1 AS n UNION ALL
SELECT 3 AS n UNION ALL
SELECT 3 AS n
) AS a

Returns (2, 3, 4, 7).
10/12/2007 1:13 PM | Rick O
Gravatar

# re: By The Way ... DISTINCT is not a function ...

Rick -- DISTINCT in that case is still not a function; it is a tag or a flag on the aggregate function indicating to only count or sum distinct values.
10/12/2007 1:16 PM | Jeff
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET