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.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.