ASA Getting Started
Selecting Aggregate Data
Restricting groups
Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you type and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.
To list all sales reps with more than 55 orders and an ID of more than 1000, type the following statement.
SELECT sales_rep, count( * ) FROM sales_order KEY JOIN employee WHERE sales_rep > 1000 GROUP BY sales_rep HAVING count( * ) > 55 ORDER BY sales_rep
The following statement produces the same results.
SELECT sales_rep, count( * ) FROM sales_order KEY JOIN employee GROUP BY sales_rep HAVING count( * ) > 55 AND sales_rep > 1000 ORDER BY sales_rep
Adaptive Server Anywhere detects that both statements describe the same result set, and so executes each efficiently.