Have you ever seen (or written) code like this:
select distinct(employeeID), salary
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)
select (employeeID), (salary)
select distinct ((employeeID)), ((salary))
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.
- 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
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!