Main Page Sitemap

Most viewed

uCertify PW0-104 CWNA practice test 8.01.05 License Key Code
Gorilla Monsoon - Wikipedia, the free encyclopediaElephants born in the high stress season have faster reproductiveAssessing the effects of anthropogenic aerosols on Pacific storm Where Tradition Meets Technology PRODUCT FEATURES Pro-Bucker™ pickups Shadow NanoMag™ pickups Native Instruments GuitarRig™ download USB, mono, stereo outputs SPECIFICATIONS Body...
Read more
FastTrack Schedule 10.0.5 patched lifetime lecense
Dahua IPC-HFW4300S 3MP Mini Bullet Review NetworkWizkids Dedicated to creating games driven by imagination Cellkraft was founded in the year 2000 developing fuel cells for advanced applications. In parallel with that development, products to solve humidification needs for laboratories and industrial customers were developed. Since...
Read more
MySQL Data Access Components Unicode for Delphi, C++Builder, and RAD Studio 2007 7.6.12 and Activator
Software product innovators for the Architecture- Engineering – Construction (AEC )vertical serving government bodies and construction enterprises SoftTech Engineers Pvt. Ltd is dedicated to innovate reliable and world class software products for the niche vertical of Architecture- Engineering- Construction (AEC ). Our products enable...
Read more

Queryshow 1.1 Serial Keygen Full

VMware Workstation 8.0.3 Setup and Activation

Sorting Information

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 queryshow 1.1 Serial Keygen Full 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 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.

SELECT query syntax diagram
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 the classes table and order by 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 andM ZIP Code from the vendors table and order by ZIP Code
SQL SELECT VendName, VendZipCode
FROM Vendors
ORDER BY VendZipCode

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.

SQL SELECT VendName, VendZipCode
FROM Vendors

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, and employee ID from the employees table and order by last name and first name
SQL SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeID
FROM Employees
ORDER BY EmpLastName, EmpFirstName

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.

SQL SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeID
FROM Employees
ORDER BY EmpLastName DESC, EmpFirstName ASC

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.

Exchange 2007 Storage SystemsThis 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.



Next Steps

Part 1: How to use the SELECT statement in SQL

Part 2: Translating information requests into SQL SELECT statements

Part 3: Using DISTINCT in SQL to eliminate duplicate rows

Part 4: Using the ORDER BY clause of the SELECT query in SQL

Part 5: SQL SELECT statement and SELECT query samples

This was first published in July 2008