Manage Learn to apply best practices and optimize your operations.

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
    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.

This was last published in January 2008

Dig Deeper on Oracle development languages



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.