MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 419 / 860

Using SQL 419
Filtering results
The SELECT statement lets you filter the results of a query to return only those records that meet
specific criteria. For example, if you want to access all database records for employees in
department 3, you use the following query:
SELECT * FROM employees WHERE DeptID=3
You can combine multiple conditions using the WHERE clause. For example, the following
example uses two conditions:
SELECT * FROM employees WHERE DeptID=3 AND Title='Engineer'
Sorting results
By default, a database does not sort the records returned from a SQL query. In fact, you cannot
guarantee that the records returned from the same query are returned in the same order each time
you run the query.
However, if you require records in a specific order, you can write your SQL statement to sort the
records returned from the database. To do so, you include an ORDER BY clause in the SQL
statement.
For example, the following SQL statement returns the records of the table ordered by the
LastName column:
SELECT * FROM employees ORDER BY LastName
You can combine multiple fields in the ORDER BY clause to perform additional sorting:
SELECT * FROM employees ORDER BY DepartmentID, LastName
This statement returns row ordered by department, then by last name within the department.
Returning a subset of columns
You might want only a subset of columns returned from a database table, as in the following
example, which returns only the FirstName, LastName, and Phone columns. This example is
useful if you are building a web page that shows the phone numbers for all employees.
SELECT FirstName, LastName, Phone FROM employees
However, this query does not to return the table rows in alphabetical order. You can include an
ORDER clause in the SQL, as follows:
SELECT the FirstName, LastName, Phone
FROM employees
ORDER BY LastName, FirstName
Using column aliases
You might have column names that you do not want to retain in the results of your SQL
statement. For example, your database is set up with a column that uses a reserved word in
ColdFusion, such as EQ. In this case, you can rename the column as part of the query, as follows:
SELECT EmpID, LastName, EQ as MyEQ FROM employees
The results returned by this query contains columns named EmpID, LastName, and MyEQ.