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: