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.

Sorting the results of a SQL query is primarily done using the `ORDER BY` clause, but there are various techniques and functions within SQL that can influence or enhance how sorting is applied. Here are some of the key techniques:

1. Basic `ORDER BY` Clause: The most straightforward technique for sorting query results. You can specify one or more columns to sort by, and the default sort order is ascending. For example, `ORDER BY column_name ASC`.
2. Descending Order: To sort the results in descending order, you use the `DESC` keyword in your `ORDER BY` clause, such as `ORDER BY column_name DESC`.
3. Sorting by Multiple Columns: You can sort by more than one column by listing the columns in the `ORDER BY` clause, separated by commas. The priority of sorting will follow the order of columns listed.
4. Using Column Position: Instead of specifying column names in the `ORDER BY` clause, you can use their positions in the select list. For instance, `ORDER BY 1, 2` would sort by the first and second columns selected.
5. Sorting by Expressions: You can sort the results by an expression, such as a calculation or concatenation of columns, rather than just the column itself.
6. Sorting by Alias: You can sort query results using an alias assigned to a column or expression within the `SELECT` statement.
7. Case-Sensitive Sorting: By default, sorting is case-insensitive in many SQL databases. However, you can force case-sensitive sorting by using binary collation or functions specific to the SQL dialect you're using.
8. Sorting with `NULLS FIRST` or `NULLS LAST`: In SQL, null values can be sorted explicitly to appear at the beginning or the end of the result set, using `NULLS FIRST` or `NULLS LAST` options.
9. Using `TOP` with `ORDER BY`: In some SQL dialects like T-SQL (used in Microsoft SQL Server), you can use the `TOP` clause in conjunction with `ORDER BY` to select a specific number of top or bottom records after sorting.
10. Window Functions: Functions like `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` can be used with an `OVER` clause that includes an `ORDER BY` to assign a unique rank number to each row based on the specified order.
11. `GROUP BY` with Aggregates and `ORDER BY`: When using aggregate functions (like `SUM`, `AVG`) with `GROUP BY`, you can use `ORDER BY` to sort the results based on aggregate calculations.
12. Using Subqueries: You can perform sorting within subqueries to control the order of rows before they are processed or joined with other tables in the outer query.

Each of these techniques can be used based on the specific requirements of your query and the result set you need to generate. The choice among them depends on factors like the complexity of the data, performance considerations, and the specific SQL dialect you're working with.

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.

In the next lesson, we will look at an example of this clause.