Sorting Query Results - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Sorting Query Results

When you use the SELECT statement, the results are returned to you in the order in which they appear in the table. This is usually the order in which the rows were added to the table. Since that probably isn't the order you want, here is how to sort the query results. To sort rows, you need to add the ORDER BY clause. ORDER BY always comes after the table name; if you try to use it before, you generate a SQL error.

Now click the SQL button, enter the SQL code shown in Listing 6.4, then click OK.

Listing 6.4. SELECT with Sorted Output

SELECT MovieTitle, PitchText, Summary
FROM Films
ORDER BY MovieTitle

Your output is then sorted by the MovieTitle column, as shown in Figure 6.15.

What if you need to sort by more than one column? No problem. You can pass multiple columns to the ORDER BY clause. Once again, if you have multiple columns listed, you must separate them with commas. The SQL code in Listing 6.5 demonstrates how to sort on more than one column by sorting by RatingID, and then by MovieTitle within each RatingID. The sorted output is shown in Figure 6.16.

Listing 6.5. SELECT with Output Sorted on More Than One Column

SELECT RatingID, MovieTitle, Summary
FROM Films
ORDER BY RatingID, MovieTitle

You also can use ORDER BY to sort data in descending order (ZA). To sort a column in descending order, just use the DESC (short for descending) parameter. Listing 6.6 retrieves all the movies and sorts them by title in reverse order. Figure 6.17 shows the output that this SQL SELECT statement generates.

Listing 6.6. SELECT with Output Sorted in Reverse Order

SELECT MovieTitle, PitchText, Summary
FROM Films
ORDER BY MovieTitle DESC