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
It's worth repeating: DISTINCT is not a function.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence. Continue Reading
SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table. Continue Reading