At the beginning of this chapter, we said that the SELECT operation can be broken down into three smaller operations: the SELECT statement, the SELECT expression, and the SELECT query. We also stated that you can combine these operations in various ways to answer complex requests. However, you also need to combine these operations in order to sort the rows of a result set.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
By definition, the rows of a result set returned by a SELECT statement are unordered. The sequence in which they appear is typically based on their physical position in the table. (The actual sequence is often determined dynamically by your database system based on how it decides to most efficiently satisfy your request.) The only way to sort the result set is to embed the SELECT statement within a SELECT query, as shown in Figure 4–10. We define a SELECT query as a SELECT statement with an ORDER BY clause. The ORDER BY clause of the SELECT query lets you specify the sequence of rows in the final result set. As you'll learn in later chapters, you can actually embed a SELECT statement within another SELECT statement or SELECT expression to answer very complex questions. However, the SELECT query cannot be embedded at any level.
Figure 4–10 The syntax diagram for the SELECT query
The ORDER BY clause allows you to sort the result set of the specified SELECT statement by one or more columns and also provides the option of specifying an ascending or descending sort order for each column. The only columns you can use in the ORDER BY clause are those that are currently listed in the SELECT clause. (Although this requirement is specified in the SQL Standard, some vendor implementations allow you to disregard it completely. However, we comply with this requirement in all the examples used throughout the book.) When you use two or more columns in an ORDER BY clause, separate each column with a comma. The SELECT query returns a final result set once the sort is complete.
First Things First: Collating Sequences
Before we look at some examples using the SELECT query, a brief word on collating sequences is in order. The manner in which the ORDER BY clause sorts the information depends on the collating sequence used by your database software. The collating sequence determines the order of precedence for every character listed in the current language character set specified by your operating system. For example, it identifies whether lowercase letters will be sorted before uppercase letters, or whether case will even matter. Check your database software's documentation, and perhaps consult your database administrator to determine the default collating sequence for your database. For more information on collating sequences, see the subsection Comparing String Values: A Caution in Chapter 6.
Let's Now Come to Order
With the availability of the ORDER BY clause, you can present the information you retrieve from the database in a more meaningful fashion. This applies to simple requests as well as complex ones. You can now rephrase your requests so that they also indicate sorting requirements. For example, a question such as "What are the categories of classes we currently offer?" can be restated as "List the categories of classes we offer and show them in alphabetical order."
Before beginning to work with the SELECT query, you need to adjust the way you define a translation statement. This involves adding a new section at the end of the translation statement to account for the new sorting requirements specified within the request. Use this new form to define the translation ¬statement.
Select <item> from the <source> and order by <column(s)>
Now that your request will include phrases such as "sort the results by city," "show them in order by year," or "list them by last name and first name," study the request closely to determine which column or columns you need to use for sorting purposes. This is a simple exercise because most people use these types of phrases, and the columns needed for the sort are usually self-evident. After you identify the appropriate column or columns, use them as a replacement for <column(s) > in the translation statement. Let's take a look at a simple request to see how this works.
"List the categories of classes we offer and show them in alphabetical order."
|Translation||Select category from the classes table and order by category|
|Clean Up||Select category from classes order by category|
In this example, you can assume that Category will be used for the sort because it's the only column indicated in the request. You can also assume that the sort should be in ascending order because there's nothing in the request to indicate the contrary. This is a safe assumption. According to the SQL Standard, ascending order is automatically assumed if you don't specify a sort order. However, if you want to be absolutely explicit, insert ASC after Category in the ORDER BY clause.
In the following request, the column needed for the sort is more clearly defined.
"Show me a list of vendor names in ZIP Code order."
|Translation||Select vendor name and ZIP Code from the vendors table and order by ZIP Code|
|Clean Up||Select vendor name ZIP Code from vendors order by ZIP Code|
In general, most people will tell you if they want to see their information in descending order. When this situation arises and you need to display the result set in reverse order, insert the DESC keyword after the appropriate column in the ORDER BY clause. For example, here's how you would modify the SELECT statement in the previous example when you want to see the information sorted by ZIP Code in descending order.
The next example illustrates a more complex request that requires a multicolumn sort. The only difference between this example and the previous two examples is that this example uses more columns in the ORDER BY clause. Note that the columns are separated with commas, which is in accordance with the syntax diagram shown in Figure 4–10.
"Display the names of our employees, including their phone number and ID number, and list them by last name and first name."
|Translation||Select last name, first name, phone number, and employee ID from the employees table and order by last name and first name|
|Clean Up||Select last name, first name, phone number, employee ID from employees order by last name first name|
One of the interesting things you can do with the columns in an ORDER BY clause is to specify a different sort order for each column. In the previous example, you can specify a descending sort for the column containing the last name and an ascending sort for the column containing the first name. Here's how the SELECT statement looks when you make the appropriate modifications.
Although you don't need to use the ASC keyword explicitly, the statement is more self-documenting if you include it.
The previous example brings an interesting question to mind: Is any importance placed on the sequence of the columns in the ORDER BY clause? The answer is "Yes!" The sequence is important because your database system will evaluate the columns in the ORDER BY clause from left to right. Also, the importance of the sequence grows in direct proportion to the number of columns you use. Always sequence the columns in the ORDER BY clause properly so that the result sorts in the appropriate order.
Saving Your Work
Save your SELECT statements—every major database software program provides a way for you to save them! Saving your statements eliminates the need to recreate them every time you want to make the same request to the database. When you save your SELECT statement, assign a meaningful name that will help you remember what type of information the statement provides. And if your database software allows you to do so, write a concise description of the statement's purpose. The value of the description will become quite clear when you haven't seen a particular SELECT statement for some time and you need to remember why you constructed it in the first place.
A saved SELECT statement is categorized as a query in some database programs and as a view, function, or stored procedure in others. Regardless of its designation, every database program provides you with a means to execute, or run, the saved statement and work with its result set.
Two common methods are used to execute a query. The first is an interactive device (such as a command on a toolbar or query grid), and the second is a block of programming code. You'll use the first method quite extensively. There's no need to worry about the second method until you begin working with your database software's programming language. Although it's our job to teach you how to create and use SQL statements, it's your job to learn how to create, save, and execute them in your database software program.
This chapter excerpt from SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez, is printed with permission from Addison-Wesley Professional, Copyright 2007.
Click here for the chapter download or purchase the book here.
Order By Clause (Visual Basic)
Specifies the sort order for a query result.
Required. One or more fields from the current query result that identify how to order the returned values. The field names must be separated by commas (,). You can identify each field as sorted in ascending or descending order by using the or keywords. If no or keyword is specified, the default sort order is ascending. The sort order fields are given precedence from left to right.
You can use the clause to sort the results of a query. The clause can only sort a result based on the range variable for the current scope. For example, the clause introduces a new scope in a query expression with new iteration variables for that scope. Range variables defined before a clause in a query are not available after the clause. Therefore, if you want to order your results by a field that is not available in the clause, you must put the clause before the clause. One example of when you would have to do this is when you want to sort your query by fields that are not returned as part of the result.
Ascending and descending order for a field is determined by the implementation of the IComparable interface for the data type of the field. If the data type does not implement the IComparable interface, the sort order is ignored.
The following query expression uses a clause to declare a range variable for the collection. The clause sorts the query result by price in ascending order (the default). Books with the same price are sorted by title in ascending order. The clause selects the and properties as the values returned by the query.
The following query expression uses the clause to sort the query result by price in descending order. Books with the same price are sorted by title in ascending order.
The following query expression uses a clause to select the book title, price, publish date, and author. It then populates the , , , and fields of the range variable for the new scope. The clause orders the new range variable by author name, book title, and then price. Each column is sorted in the default order (ascending).
Introduction to LINQ in Visual Basic