Home > Articles

  • Print
  • + Share This
This chapter is from the book

Specifying Sort Direction

Data sorting is not limited to ascending sort orders (from A to Z). Although this is the default sort order, the ORDER BY clause can also be used to sort in descending order (from Z to A). To sort by descending order, the keyword DESC must be specified.

The following example sorts the products by price in descending order (most expensive first):

INPUT

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

OUTPUT

prod_id  prod_price  prod_name
-------  ----------  --------------------
BR03     11.9900     18 inch teddy bear
RYL01    9.4900      King doll
RYL02    9.4900      Queen doll
BR02     8.9900      12 inch teddy bear
BR01     5.9900      8 inch teddy bear
RGAN01   4.9900      Raggedy Ann
BNBG01   3.4900      Fish bean bag toy
BNBG02   3.4900      Bird bean bag toy
BNBG03   3.4900      Rabbit bean bag toy

But what if you were to sort by multiple columns? The following example sorts the products in descending order (most expensive first), plus product name:

INPUT

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

OUTPUT

prod_id  prod_price  prod_name
-------  ----------  --------------------
BR03     11.9900     18 inch teddy bear
RYL01    9.4900      King doll
RYL02    9.4900      Queen doll
BR02     8.9900      12 inch teddy bear
BR01     5.9900      8 inch teddy bear
RGAN01   4.9900      Raggedy Ann
BNBG02   3.4900      Bird bean bag toy
BNBG01   3.4900      Fish bean bag toy
BNBG03   3.4900      Rabbit bean bag toy

ANALYSIS

The DESC keyword only applies to the column name that directly precedes it. In the example above, DESC was specified for the prod_price column, but not for the prod_name column. Therefore, the prod_price column is sorted in descending order, but the prod_name column (within each price) is still sorted in standard ascending order.

TIP

Sorting Descending on Multiple Columns If you want to sort descending on multiple columns, be sure each column has its own DESC keyword.

It is worth noting that DESC is short for DESCENDING, and both keywords may be used. The opposite of DESC is ASC (or ASCENDING), which may be specified to sort in ascending order. In practice, however, ASC is not usually used because ascending order is the default sequence (and is assumed if neither ASC nor DESC are specified).

TIP

Case Sensitivity and Sort Orders When you are sorting textual data, is A the same as a? And does a come before B or after Z? These are not theoretical questions, and the answers depend on how the database is set up.

In dictionary sort order, A is treated the same as a, and that is the default behavior for most Database Management Systems. However, most good DBMSs enable database administrators to change this behavior if needed. (If your database contains lots of foreign language characters, this might become necessary.)

The key here is that if you do need an alternate sort order, you cannot accomplish it with a simple ORDER BY clause. You must contact your database administrator.

  • + Share This
  • 🔖 Save To Your Account