MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 470 / 860

470 Chapter 22: Using Query of Queries
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
Using aliases
ColdFusion supports the use of database column aliases. An alias is an alternate name for a
database field or value. ColdFusion lets you reuse an alias in the same SQL statement.
One way to create an alias is to concatenate (append) two or more columns to generate a value.
For example, you can concatenate a first name and a last name to create the value fullname.
Because the new value does not exist in a database, you refer to it by its alias. The AS keyword
assigns the alias in the SELECT statement.
Examples
ColdFusion supports alias substitutions in the ORDER BY, GROUP BY, and HAVING clauses.
Note: ColdFusion does not support aliases for table names.
SELECT Fir ' ' + LastName AS fullname
from Employee;
The following examples rely on these two master queries:
<cfquery name="employee" datasource="2pubs">
SELECT * FROM employee
</cfquery>
<cfquery name="roysched" datasource="2pubs">
SELECT * FROM roysched
</cfquery>
To generate output for the following examples, use the
cfdump
tag. For example, use
<cfdump
var="#order_by#">
for the following ORDER BY example.
ORDER BY example
<cfquery name="order_by" dbtype="query">
SELECT ( job_lvl)/2 AS job_value
FROM employee
ORDER BY job_value
</cfquery>
GROUP BY example
<cfquery name="group_by" dbtype="query">
SELECT hirange AS x, count(hirange)
FROM roysched
GROUP BY x
</cfquery>