ASA SQL User's Guide
  Summarizing, Grouping and Sorting Query Results
    Standards and compatibility

Compatibility with Adaptive Server Enterprise

Adaptive Server Enterprise supports several extensions to the GROUP BY clause that are not supported in Adaptive Server Anywhere. These include the following:

• Non-grouped columns in the select list    Adaptive Server Enterprise permits column names in the select list that do not appear in the group by clause. For example, the following is valid in Adaptive Server Enterprise:

  SELECT name, unit_price
  FROM product
  GROUP BY name

  This syntax is not supported in Adaptive Server Anywhere.

• Nested aggregate functions    The following query, which nests a vector aggregate inside a scalar aggregate, is valid in Adaptive Server Enterprise but not in Adaptive Server Anywhere:

  SELECT MAX(AVG(unit_price))
  FROM product
  GROUP BY name

• GROUP BY and ALL    Adaptive Server Anywhere does not support the use of ALL in the GROUP BY clause.

• HAVING with no GROUP BY    Adaptive Server Anywhere does not support the use of HAVING with no GROUP BY clause unless all the expressions in the select and having clauses are aggregate functions. For example, the following query is valid in Adaptive Server Enterprise, but is not supported in Adaptive Server Anywhere:

  --unsupported syntax
  SELECT unit_price
     FROM product
     HAVING COUNT(*) > 8

  However, the following statement is valid in Adaptive Server Anywhere, because the functions MAX and COUNT are aggregate functions:

  SELECT MAX(unit_price)
  FROM product
  HAVING COUNT(*) > 8

• HAVING conditions    Adaptive Server Enterprise supports extensions to HAVING that allow non-aggregate functions not in the select list and not in the GROUP BY clause. Only aggregate functions of this type are allowed in Adaptive Server Anywhere.

• DISTINCT with ORDER BY or GROUP BY    Adaptive Server Enterprise permits the use of columns in the ORDER BY or GROUP BY clause that do not appear in the select list, even in SELECT DISTINCT queries. This can lead to repeated values in the SELECT DISTINCT result set. Adaptive Server Anywhere does not support this behavior.

• Column names in UNIONS    Adaptive Server Enterprise permits the use of columns in the ORDER BY clause in unions of queries. In Adaptive Server Anywhere, the ORDER BY clause must use an integer to mark the column by which the results are being ordered.