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
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
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.
Oracle White Papers: Fusion Middleware