Re: Daily SQL quiz - revisited

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. Also the SELECT * is not permitted with a GROUP BY for the same reason (all columns in a GROUP BY query must either be aggregated or in the GROUP BY). I have posted the solution in another response and it uses a correlated subquery. Jon Rosen