Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Compatibility with Adaptive Server Enterprise

Adaptive Server Enterprise supports several extensions to the GROUP BY clause that are not supported in SQL 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, UnitPrice
     FROM Products
     GROUP BY Name;

  This syntax is not supported in SQL 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 SQL Anywhere:

  SELECT MAX( AVG( UnitPrice ) )
     FROM Products
     GROUP BY Name;

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

• HAVING with no GROUP BY    SQL 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 SQL Anywhere:

  SELECT UnitPrice
     FROM Products
     HAVING COUNT(*) > 8;

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

  SELECT MAX( UnitPrice )
     FROM Products
     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 SQL 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. SQL 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 SQL Anywhere, the ORDER BY clause must use an integer to mark the column by which the results are being ordered.