Re: Daily SQL quiz - revisited

In article <1993May14.060018.18547@RedBrick.COM>, jfr@RedBrick.COM (Jon Rosen) writes: >In article <1993May13.182749.14450@stsci.edu> davids@stsci.EDU (David Silberberg) writes: >>In article <1993May13.170650.27426@wvus.org>, sford@wvus.org (Scott Ford) writes: >> >>[munch] >>what about the following: >> >>select * from mytable >>group by key_col >>having sequence = max(sequence)>> > > This only works in Sybase because they are not ANSI-SQL compliant and they > "bastardize" the execution of subqueries. In ANSI SQL, this is not a > legal query because any column in a HAVING clause must either be an > aggregate (as max(sequence) is) or be in the GROUP BY clause (which > sequence is NOT). Sybase does this by performing a sort of weird join > of the original rows to the grouped results. ANSI does NOT permit this > in this form. this is not true. ANSI permits an implementation to process non-conforming language any way they please. this is non conforming language, and what sybase does with it is quite useful for columns that have a functional dependance on the groupby field/s. there are FIPS requirements that an implementation provide a "flagger" for non-conforming language (ie it should gripe at you if you use some) (at least thats the way it was when i was attending X3H2 sql meetings, perhaps some language lawyer will correct me with the proper citations from the standard) on the other hand, if you meant to say that this solution "is not portable" amongst ANSI-SQL implementations, then i agree with you. There is at least one other SQL implementation that solves this class of problems elegantly - SAS's PROC SQL :-) -- Paul Kent (Base R&D) " nothing ventured, nothing disclaimed " kent@unx.sas.com SAS Institute Inc, SAS Campus Dr, Cary NC 27513-2414.