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.

Figure 6.15. You use the ORDER BY clause to sort SELECT output.

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 

Figure 6.16. You can sort output by more than one column via the ORDER BY clause.

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 

Figure 6.17. Using the ORDER BY clause, you can sort data in a descending sort sequence.