4 Sorting, skipping, and commenting data

Relational databases do not guarantee any natural order of query results, so if you care about sequence you must ask for it explicitly. The chapter introduces ORDER BY as the way to sort results, emphasizing that it runs last in the query and should appear at the end of the statement. You can sort by one or multiple columns, control direction with ASC and DESC (and it’s wise to state the direction explicitly), and even sort using column aliases or columns that aren’t selected. There’s also a shorthand to sort by column positions in the SELECT list, but it’s discouraged for reusable code due to poor readability and fragility. Because sorting forces the database to organize the entire result set, the chapter cautions that ORDER BY can be expensive on very large datasets.

To reduce result size, the chapter shows how to use LIMIT to return only a specified number of rows, and explains why pairing it with ORDER BY is usually essential for meaningful, repeatable samples (such as “most recent” or “highest priced”). OFFSET can be combined with LIMIT to skip a number of rows—useful for paging or to look beyond the very top results—though OFFSET cannot be used without LIMIT. The text also notes SQL dialect differences: LIMIT is supported in engines like MySQL, MariaDB, PostgreSQL, and SQLite, but not in DB2, Oracle, or SQL Server, which use different, vendor-specific mechanisms.

Finally, the chapter promotes clear, consistent commenting practices so people (including your future self) understand a query’s intent. It covers single-line comments with --, the less common # (supported in MySQL), and multiline comments with /* ... */ that can span blocks of SQL or temporarily disable code. Good comments can include author, date, purpose, and clarifications of nonobvious choices, improving readability and saving substantial time when revisiting or sharing queries.

The results of the query of TitleName and Price from the title table. The results are not ordered by either TitleName or Price.

The results of the preceding query, with the results now sorted alphabetically by TitleName.

Using an ORDER BY, the results of the preceding query are now ordered by TitleName, which has been aliased as NameOfTheBook.

The results of a query ordered by the Advance column and then by the Royalty column, both from highest value to lowest.

The results now sorted by Advance descending and Royalty ascending.

shows only the TitleName values in the results, but the rows are still sorted by Advance descending and Royalty ascending.

The results of using LIMIT to return only three rows.

The three most recently published TitleNames and their PublicationDates when using ORDER BY on PublicationDate and LIMIT.

The most recent three TitleName and PublicationDate values after skipping the first row using OFFSET.

1. The answer is below:

2. The answer is below:

• Alternatively, you could use SELECT * instead of listing all the column names.

3. Because the entire query is now on a single line preceded by two hyphens, the entire query will execute as a comment. Although this will not result in an error, it will also not return the results the query intended. This is one of several reasons why /* and */ are advised to be used for comments in reusable code, as both the beginning and end of the commented line or lines are clearly marked.

FAQ