5. Sorting Data

Keywords Introduced

ORDER BY • ASC • DESC

The ability to present data in a sorted order is often essential to the task at hand. For example, if an analyst is shown a large list of customers in a random order, they’d probably find it difficult to locate one particular customer. However, if the same list is sorted alphabetically, the desired customer can quickly be located.

The idea of sorting data applies to many situations, even when the data isn’t alphabetic in nature. For example, orders can be sorted by order date, allowing one to rapidly find an order taken at a particular date and time. Alternatively, orders can be sorted by the order amount, allowing orders to be viewed from the smallest to largest. No matter what particular form a sort takes, it can add a useful way of organizing the data being presented to an end user.

Sorting in Ascending Order

Up until now, data has not been returned in any particular order. When a SELECT is issued, you never know which row will come first. If the query is executed from within a software program, and no one ever sees the data at that point in time, then it really doesn’t matter. But if the data is to be immediately displayed to a user, then the order of rows is often significant. A sort can be easily added to a SELECT statement by using an ORDER BY clause.

Here’s the general format for a SELECT statement with an ORDER BY clause:

SELECT columnlist
FROM tablelist
ORDER BY columnlist

The ORDER BY clause is always placed after the FROM clause, which in turn always comes after the SELECT keyword. The italicized columnlist for the SELECT and ORDER BY keywords indicates that any number of columns can be listed. The columns in columnlist can be individual columns or more complex expressions. Also, the columns specified after the SELECT and ORDER BY keywords can be entirely different columns. The italicized tablelist indicates that any number of tables can be listed in the FROM clause. The syntax for listing multiple tables will be introduced in Chapter 11, “Inner Joins” and Chapter 12, “Outer Joins.”

For the following examples on sorting, we’ll work from data in this Salespeople table:

Image

To sort data in an alphabetic order, with A coming before Z, we simply need to add an ORDER BY clause to the SELECT statement. For example:

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY LastName

brings back this data:

Image

Because there are two Browns, Iris and Carla, there’s no way to predict which one will be listed first. This is because we are sorting only on LastName, and there are multiple rows with that same last name.

Similarly, if we issue this SELECT:

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY FirstName

then this data is retrieved:

Image

The order is now completely different, because the sort is by first name rather than last name.

SQL provides a special keyword named ASC, which stands for ascending. This keyword is completely optional and largely unnecessary, because all sorts are assumed to be in ascending order by default. The following SELECT, which uses the ASC keyword, returns the same data shown previously.

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY FirstName ASC

In essence, the keyword ASC can be used to emphasize the fact that the sort is in ascending, rather than descending, order.

Sorting in Descending Order

The DESC keyword sorts in an order opposite to ASC. Instead of ascending, the order in such a sort is descending. For example:

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY FirstName DESC

retrieves:

Image

The first names are now in a Z-to-A order.

Sorting by Multiple Columns

We now return to the problem of what to do with the Browns. To sort by last name when there is more than one person with the same last name, we must add a secondary sort by first name, as follows:

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY LastName, FirstName

This brings back:

Image

Because a second sort column is now specified, we can now be certain that Carla Brown will appear before Iris Brown. Note that LastName must be listed before FirstName in the ORDER BY clause. The order of the columns is significant. The first column listed always has the primary sort value. Any additional columns listed become secondary, tertiary, and so on.

Sorting by a Calculated Field

We’ll now apply our knowledge of calculated fields and aliases from Chapter 3 to illustrate some further possibilities for sorts. This statement:

SELECT
LastName + ‘, ‘ + FirstName AS ‘Name’
FROM Salespeople
ORDER BY Name

returns this data:

Image

As seen, we utilized concatenation to create a calculated field with an alias of Name. We are able to refer to that same column alias in the ORDER BY clause. This nicely illustrates another benefit of using column aliases. Also, note the design of the calculated field itself. We inserted a comma and a space between the last name and first name columns to separate them, and to show the name in a commonly used format. Conveniently, this format works well for sorting. The ability to display names in this format, with a comma separating the last and first name, is a handy trick to keep in mind. Users very often want to see names arranged in this manner.

It’s also possible to put a calculated field in the ORDER BY clause without also using it as a column alias. Similar to the above, we could also specify:

SELECT
FirstName,
LastName
FROM Salespeople
ORDER BY LastName + FirstName

This would display:

Image

The data is sorted the same as in the prior example. The only difference is that we’re now specifying a calculated field in the ORDER BY clause without making use of column aliases. This gives the same result as if LastName and FirstName were specified as the primary and secondary sort columns.

Sort Sequences

In the previous examples, all of the data is character data, consisting of letters from A to Z. There are no numbers or special characters. Additionally, there has been no consideration of upper and lowercase letters.

Every database lets users specify or customize collation settings that provide details on how data is sorted. The settings vary among databases, but three facts are generally true. First, when data is sorted in an ascending order, any data with NULL values appear first. As previously discussed, NULL values are those where there is an absence of data. After any NULLs, numbers will appear before characters. For data sorted in descending order, character data will display first, then numbers, and then NULLs.

Second, for character data, there is usually no differentiation between upper and lowercase. An e is the treated the same as an E. Third, for character data, the individual characters that make up the value are evaluated from left to right. If we’re talking about letters, then AB will come before AC. Let’s look at an example, taken from this table, which we’ll refer to as TableForSort:

Image

In this table, the CharacterData column is defined as a character column, for example as VARCHAR (a variable length datatype). Similarly, the NumericData column is defined as a numeric column, such as INT (an integer datatype). Values with no data are displayed as NULL. When this SELECT is issued against the TableForSort table:

SELECT
NumericData
FROM TableForSort
ORDER BY NumericData

it will display:

Image

Notice that NULLs come first, then the numbers in numeric sequence. If we want the NULL values to assume a default value of 0, we can use the ISNULL function discussed in the previous chapter and issue this SELECT statement:

SELECT
ISNULL(NumericData,0) AS ‘NumericData’
FROM TableForSort
ORDER BY ISNULL(NumericData,0)

The result is now:

Image

The ISNULL function converted the NULL value to a 0, which results in a different sort order.

Of course, the decision as to whether to display NULL values as NULL or as 0 depends on the circumstance. If the user thinks of NULL values as meaning 0, then they should be displayed as 0. However, if the user sees NULL values as an absence of data, then displaying the word NULL is appropriate.

Turning to a different ORDER BY clause against the same table, if we issue this SELECT:

SELECT
CharacterData
FROM TableForSort
ORDER BY CharacterData

it will display:

Image

As expected, NULLs come first, then values with numeric digits, and then values with alphabetic characters. Notice that 23 comes before 5. This is because the 23 and 5 values are being evaluated as characters, not as numbers. Because character data is evaluated from left to right and 2 is lower than 5, 23 is displayed first.

Looking Ahead

In this chapter, we talked about the basic possibilities for sorting data in a specific order. We illustrated how to sort by more than one column. We also discussed the use of calculated fields in sorts. Finally, we covered some of the quirks of sorting, particularly when it comes to data with NULL values and with numbers in character columns.

At the beginning of the chapter, we mentioned some of the general uses for sorts. Primary among these is the ability to simply place data in an easily understood order, thus allowing users to quickly locate a desired piece of information. People generally like to see data in some kind of order, and sorts accomplish that goal. Another interesting use of sorts will be covered in Chapter 6, “Selection Criteria.” In that chapter, we’ll introduce the keyword TOP and another way to use sorts in conjunction with that keyword. This technique, commonly known as a Top N sort, allows us to do such things as display customers with the five highest orders for a given time period.

In our next chapter, we’ll move beyond our analysis of what can be done with columnlists and discuss data selection. The ability to specify selection criteria in SELECT statements is critical to most normal queries. In the real world, it would be very unusual to issue a SELECT statement without some sort of selection criteria. The topics discussed in the next chapter address this important topic.

Sorting in Ascending Order

Sorting in Descending Order

Sorting by Multiple Columns

Sorting by a Calculated Field

Sort Sequences

Looking Ahead

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
44.202.90.91