Sorting in Ascending or Descending Order

I would like a list of titles along with the total sales year to date. I am curious about which titles have sold the most books. Suppose I prepare a query like this one:

     select title_id, ytd_sales
     from titles
     order by ytd_sales

    title_id    ytd_sales
     --------   ----------
     MC3026     (null)
     PC9999     (null)
     PS2106     111
     PS1372     375
     TC3218     375
     […]
     PC1035     8780
     TC4203     15096
     BU2075     18722
     MC3021     22246

     (18 row(s) affected)

This query produces a list of books, with the hot sellers listed at the bottom of the list and the non-performers at the top.

I would much rather have a report that puts my best foot forward, so I’ll sort it instead in descending order. This query is just like the first, except that it sorts in descending order:

     select title_id, ytd_sales
     from titles
     order by ytd_sales desc

     title_id      ytd_sales
     --------      ----------
     MC3021        22246
     BU2075        18722
     TC4203        15096
     […]
     TC3218        375
     PS2106        111
     MC3026       (null)
     PC9999       (null)

     (18 row(s) affected)

All titles are listed, with those selling the most listed first and those selling the least listed last. Note that in both output samples, there are two titles that have no values listed for ytd_sales; that is, they contain null values. SQL Server always sorts null values as if they are the lowest possible value, so they will come first in an ascending sort and last in a descending sort.

---

Technical Note:  When the server is asked to resolve an ORDER BY, it looks first to see if an index exists on that column. If it does, the index can be used to resolve the sort very quickly. After all, the whole point of an index is to maintain data in a specific order so that certain rows can be found quickly.

When an index doesn’t exist on a sorted column, a temporary worktable is used to sort the rows involved in the query.

When a descending ORDER BY is requested from Microsoft SQL Server, it is capable of walking the index backwards. It is not necessary to create a descending index. (Not only is it not necessary, it’s also not possible.)

---

Sorting on Columns That Do Not Appear in the Result Set

Although the situation seldom arises, it is not necessary for a column to appear in the result set in order to use ORDER BY on it. To create a report listing a book’s title_id and the tax amount on the book, I could use this query:

     select title_id, price * $0.08 “Book tax”
     from titles
     order by  price

     title_id Book tax
     --------      -----------------
     MC3026        (null)
     PC9999        (null)
     BU2075        0.24
     MC3021        0.24
     PS2106        0.56
     PS7777        0.64
     PS2091        0.88
     BU1111        0.96
     TC4203        0.96
     TC7777        1.20
     BU1032        1.60
     BU7832        1.60
     MC2222        1.60
     PS3333        1.60
     PC8888        1.60
     TC3218        1.68
     PS1372        1.73
     PC1035        1.84

     (18 row(s)   affected)

Although the price column is not actually in the result set—the tax amount is a derived column—I can still sort on it and achieve the same sort order as if I had sorted on price * $0.08 (and with better performance).

Although it is possible to order on any column or on a derived result (…ORDER BY (ytd_sales * 6) + (3.01 * price), for example), it is confusing to see data that is ordered by a hidden column. In most cases, if you plan to sort a result set, the column responsible for the data ordering should be included in the results.

In this example, I multiplied by a money data type ($0.08) so that the output I got looked like money (that is, with two digits after the decimal). If you run this query, try it both with the dollar sign and without to see the difference. If you don’t use the dollar sign, four digits will be displayed after the decimal.

An order by Shortcut

Instead of providing the name of a column in your ORDER by clause, you may give the column number instead. This next example is from earlier in the chapter, but it uses the ordinal ORDER by method:

     select title_id, ytd_sales
     from titles
     order by  2 desc

     title_id     ytd_sales
     -------      ----------
     MC3021       22246
     BU2075       18722
     TC4203       15096
     […]
     TC3218       375
     PS2106       111
     MC3026       (null)
     PC9999       (null)

     (18 row(s) affected)

This SQL demonstrates a shorthand way to order result sets.