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.
