User Guide

Query of Queries user guide 507
Using group by and having expressions
ColdFusion supports the use of any arbitrary arithmetic expression, as long as it is referenced by
an alias.
Examples
The following code is correct:
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY midrange;
The following code is correct:
SELECT (lorange+hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000;
The following code is not supported in Query of Queries:
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY (lorange + hirange)/2;
Using ORDER BY clauses
ColdFusion supports the ORDER BY clause to sort. Make sure that it is the last clause in your
SELECT statement. You can sort by multiple columns, by relative column position, by
nonselected columns. You can specify a descending sort direction with the DESC keyword (by
default, most RDBMS sorts are ascending, which makes the ASC keyword unnecessary).
Syntax
order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]
Examples
The following example shows a simple sort using an ORDER BY clause:
SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY dopamine_levels
The following example shows a more complex sort; results are first sorted by ascending levels of
dopamine, then by descending levels of acetylcholine. The ASC keyword is unnecessary, and is
used only for legibility.
SELECT acetylcholine_levels, dopamine_levels
FROM results
ORDER BY 2 ASC, 1 DESC