The Language of SQL- P13 ppsx
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (129.78 KB, 5 trang )
This page intentionally left blank
chapter 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 you were shown a large list of customers in a random order, you’d
find it difficult to locate any one particular customer. However, if the same list
were sorted alphabetically, then you could quickly locate the desired customer.
The idea of sorting data alphabetically applies to many situations, even when the
data isn’t strictly alphabetic in nature. For example, you may want to sort a list of
orders by the order date and tim e to allow you to rapidly find an order taken at a
particular date and time. Or you might want to sort a list of orders by the order
amount, to allow you to view orders from the smallest to the largest. No matter
what particular form your sort takes, it adds a useful way to organize your data as
it is being presented to the end user.
Adding a Sort
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
added easily to a
SELECT statement by using an ORDER BY clause.
47
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 after the FROM clause, which, in turn, is always 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. 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,
although you have not yet seen the syntax for listing multiple tables.
Turning to an example, you’ll be working from data in this Customers table:
CustomerID FirstName LastName
1 William Smith
2 Janet Smith
3 Natalie Lopez
4 Brenda Harper
Sorting in Ascending Order
If you want to sort data in alphabetic order, with A coming before Z, then you
simply need to add an
ORDER BY clause to the SELECT. For example:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY LastName
brings back this data:
FirstName LastName
Brenda Harper
Natalie Lopez
William Smith
Janet Smith
Chapter 5
■
Sorting Data48
Since there are two Smiths, William and Janet, there’s no way to predict which
one will be listed first. This is because you are only sorting on LastName, and
there are multiple rows with the same last name.
Similarly, if you issue this
SELECT:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName
then this data is retrieved:
FirstName LastName
Brenda Harper
Janet Smith
Natalie Lopez
William Smith
The order is now completely different since you’re sorting by first name.
SQL provides a special keyword named
ASC, which stands for ascending. This
keyword is completely optional and largely unnecessary since all sorts are
assumed to be in ascending order by default. The following
SELECT, which uses
the
ASC keyword, returns the same data as shown previously:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName ASC
The keyword ASC is used to emphasize the fact that the sort is ascending, as
opposed to descending.
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.
Sorting in Descending Order 49
For example:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName DESC
retrieves:
FirstName LastName
William Smith
Natalie Lopez
Janet Smith
Brenda Harper
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 Smiths. If you want to sort
by last name, but there are two people with the same last name, you need to add a
secondary sort by first name, as follows:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY LastName, FirstName
This brings back:
FirstName LastName
Brenda Harper
Natalie Lopez
Janet Smith
William Smith
Since you are specifying a second sort column, you can now be certain that Janet
Smith will appear before William Smith. Note that the
ORDER BY clause needs to
list LastName before FirstName. The order of the columns is significant. Your
Chapter 5
■
Sorting Data50
