DISTINCT function(???) as in SELECT DISTINCT(column)....
905411Dec 14 2011 — edited Dec 15 2011Someone in my company asked me what the following query did:
SELECT DISTINCT(col1), col2
FROM tbl;
This was used in one of our customer's queries. This is a redacted version but it conveys the question. When I run this in Oracle11g, it appears to act precisely like:
SELECT DISTINCT col1, col2
FROM tbl;
If you enter:
SELECT DISTINCT(col1), DISTINCT(col2)
FROM tbl;
you get an error saying "missing expression".
If you enter:
SELECT DISTINCT(col1), COUNT(*)
FROM tbl;
you get "not a single-group group function" pointing at DISTINCT.
DISTINCT as a function is not documented anywhere, yet the server accepts the syntax. Can anyone offer a "rationale" for why apparently invalid syntax (this is not legal in ANSI SQL) is permitted but not documented anywhere?
Thanks!