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. 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 You guessed correctly that I am a Sybase user. I must admit that I have been using Sybase so long that I didn't realize that this query was Sybase specific. What are the underlying principles of standard SQL that govern what is legal in the GROUP BY/HAVING clause? David Silberberg