How to use DISTINCT on just one column
SQL expert Rudy Limeback explains how to use DISTINCT on just one column.
Is there any way to use DISTINCT on just one column of a multi-column query? There have been numerous times I have wanted to do this, but have not found an easy way. In this instance I am selecting four columns and just want to distinct on the first column because the data of the four is different enough it returns duplicates of the first column.
The answer to your question is yes, there is a way. Specify that column in the GROUP BY clause. Base every other expression in the SELECT on aggregate values only.
select column1
, min(columns2)
, count(columns3) + 3
, sum(columns2*column4)
, avg(columns4)/count(*)
from yourtable
group
by column1
The rule of thumb is: for every distinct combination of values you want (i.e. only one row per distinct such-and-such), put those columns into both the SELECT and the GROUP BY. Everything else in the SELECT must be based on aggregate expressions.
