Q
Problem solve Get help with specific problems with your technologies, process and projects.

DISTINCT applies to all columns in the result

Could you tell me what are the effects of using the DISTINCT keyword before a group of different column names in a SELECT statement?

Could you please tell me what are the effects of using the DISTINCT keyword before a group of different column...

names in a SELECT statement? Recently, I created a query with DISTINCT in front of the first column in a list of three, and to my pleasant surprise, it had the effect of returning only results in which the three-column combination was unique, which is exactly what I wanted, but didn't expect to achieve. Does the DISTINCT keyword act on all columns included after it, or all combinations of them? Any wisdom you could provide would be greatly appreciated.

Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.

It's a never-ending source of wonder for me how so many people (not you!) think that DISTINCT applies only to one column. I've seen queries like this:

SELECT DISTINCT(T1.id), T2.id, T2.amt 
FROM T1 JOIN T2 ON T2.T1id = T1.id

The intent here, of course, was to obtain only one row per T1.id. However, the above query is exactly the same as this one:

SELECT DISTINCT T1.id, T2.id, T2.amt 
FROM T1 JOIN T2 ON T2.T1id = T1.id

When you realize that the relationship of T1 to T2 is one-to-many, it's obvious that this query will return more than one row for each T1.id.

It's worth repeating: DISTINCT is not a function.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close