ANY and ALL would be useful as summary functions in PROC SQL.
One could write
SELECT SEX, ALL(age < 18) as allMinors
GROUP BY SEX
SELECT SEX, MIN(age < 18) as allMinors
SELECT SEX, ANY(height >= 6*12) as someBasketballHope
SELECT SEX, MAX(height >= 6*12) as someBasketballHope
ANY(A), where A is boolean, would return TRUE if at least one of the non-missing values of A within the group is TRUE, and FALSE otherwise
ALL(A), where A is boolean, would return FALSE if at least one of the non missing values of A within the group is FALSE and TRUE otherwise.
ALL and ANY would show intent more clearly than MIN and MAX in the above examples and would not depend on the internal representation of boolean values.
Not sure I follow. From this list:
The min would return 19, what would all() return? Is it based on T-SQL, if so would there not be confusion then based on different platform specific SQL interpretations.
First, I don't think we should change MIN to ALL and MAX to ANY, MIN and MAX have intuitive definitions as aggregate functions, this shouldn't change.
In these examples MIN and MAX are being applied to Boolean values, The use and the return value of MIN and MAX are dependent on the Boolean values calculated during the execution of the code. Your statement involving MIN and MAX and their not depending "on the internal representation of Boolean values" makes no sense and is a bad argument.
I think I see your idea and it's interesting, but you haven't motivated your point well enough. Please put into words how you propose to define ANY and ALL as aggregate functions.
Thanks for the comments. My proposal is not about removing the MIN and MAX functions. It is just about not applying them to boolean values.
I added a more precise definition of the ALL and ANY aggregate functions. Hoping it will shift your vote .
now the only problem is that its not part of any SQL standard (that I know of, correct me if). That most likely is a deal killer -- I think.
While I can see your point, no new feature is added.
If we were to extend SQL's vocabulary, I'd much prefer a new feature such a summary function for strings ( concatenate, concatenate_dedupe for example) or an implementation of oracle's rank function.
I would like the concatenation function too! My request is modest in comparison. Nonetheless, clarity, ease of expression and legibility ARE features of a programming language. - PG
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.