Re: Daily SQL quiz - revisited

In article <1993May14.183138.17240@stsci.edu> davids@stsci.EDU (David Silberberg) writes: >In article <1993May14.060018.18547@RedBrick.COM>, jfr@RedBrick.COM (Jon Rosen) 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. > >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? The use of the GROUP BY creates what is called in the ANSI SQL parlance to be a "grouped table". That is, each row consists of values from a set of rows, all of which have the same grouping values. The grouping values are defined as those columns specified in the GROUP BY clause. Since the columns in the GROUP BY clause are by definition the same value for any given row, those columns can be used singly. All other columns may (and in fact generally will) be made up of values from many rows (the rows in the group). There are only six ways to consolidate those values into a single value so that the grouped row is consistent: the five aggregate operators (MIN, MAX, SUM, COUNT, AVG) or using a constant value. Thus, SQL's definition requires that any columns used in a select list in a statement which has a GROUP BY clause must either be: a) in the GROUP BY clause b) be the subject of an aggregate c) be a constant value for the row Interestingly enough, be aware that (c) does NOT mean that the value must be a literal (like 7.5 or 'HELLO'). It can also be an expression which is, by definition, a constant value. This means (not very obviously) that an expression like 2*WEIGHT where WEIGHT is used in the GROUP BY clause is a legal "constant value" because since WEIGHT must be a single value for the row, the expression is also a single value for the row. Furthermore, in ANSI SQL-92, the use of subqueries is extended to the SELECT list. A subquery with NO outer references is also a constant value (in fact, it is constant across all rows), and a subquery with outer references, where all the outer references are in the GROUP BY is also a constant value. So (c) items can indeed be much more complex than merely literals. Another wrinkle is to be careful not to mix valid and invalid columns in an expression. For instance, consider the following expressions: SUM(PRICE*QTY)/SUM(QTY) legal (b) - this is a weighted average SUM(PRICE)/QTY legal if QTY is in the GROUP BY clause illegal otherwise Hope this helps. Jon Rosen