Sorting Query Results using SQL

Over this and the next several lessons, we will be covering the different uses of the ORDER BY clause.
Returning results is a simple task with SQL, as you have seen and experienced thus far in this course.
You might wonder, however, how that information can be readily usable to you. After all, it's just a series of rows that meet your WHERE clause's criteria. How can you make the information more usable, more user-friendly?
One of the first things you can do is to sort the information, resulting in a set of information that is ordered based on the column or columns you specify. This alone can make the information far more meaningful, especially if you are looking for trends or doing other types of analysis.
In the next lesson, we will look at an example of this clause.

What about alternate indexing, other than foreign key indexes? Without applications under development or a database in production, it is unwise to make a guess at what alternate indexing will be needed. And it might even be important to stress that it is necessary to resist guessing at further alternate indexing, to avoid overindexing. Over indexing and creating unnecessary alternate indexes can cause more problems than it solves, particularly in a highly normalized and concurrent OLTP database model, and its fully dependent applications. Some of the best OLTP database model designs often match most (if not all) indexing requirements, using only existing primary and foreign key structures. In other words, applications are built around the normalized table structure, when an OLTP database model is properly designed. Problems occur when reporting are required in applications. This is actually quite common.

Problems do, however, appear when a user wants to sort results. For example, a buyer might want to sort a report of the SELLER and HISTORY tables join, by a date value, such as the date of each comment made about the seller. That would be the COMMENT_DATE on the HISTORY table, as in the following query:

SELECT S.SELLER, H.COMMENT_DATE,
H.FEEDBACK_POSITIVE, H.FEEDBACK_NEUTRAL, H.FEEDBACK_NEGATIVE
FROM SELLER S JOIN HISTORY H USING (SELLER_ID)
ORDER BY H.COMMENT_DATE ASCENDING;

It is conceivable that an alternate index could be created on the HISTORY.COMMENT_DATE field. As already stated, this can be very difficult to assess in analysis and design and is best left for later implementation phases. You can never accurately predict what users will want. Creating alternate indexing for possible reporting, or even brief OLTP database on-screen listing is extremely difficult without

1. developer,
2. programmer,
3. administrator, and
4. customer feedback.