Lesson 4

The basic SELECT statement, with no ORDER BY clause, returns rows in no guaranteed order at all, often the order they happen to be stored in, but SQL Server makes no promise of that. Most of the time you will want results arranged in a specific order instead. The ORDER BY clause sorts the results by one or more columns, ascending or descending, and it comes at the end of a statement, after every other clause this module has covered.

Suppose you want to sort VirtualBookShelf's Inventory table by author. The following statement does exactly that.

SELECT * FROM Inventory
ORDER BY Author

Unless otherwise specified, columns are sorted in ascending order by default: smallest to largest for numbers, oldest to newest for dates, and alphabetical for text. Sorting in descending order takes an explicit keyword, DESC, added right after the column name; ascending order can also be stated explicitly with ASC, though it's never required, since it's already what happens without it.

This is the third of this module's three original objectives, after retrieving every row with a plain SELECT in Lesson 2 and narrowing those rows down with WHERE in Lesson 3. Nothing about those two lessons changes here; ORDER BY adds to a statement that already filters, it doesn't replace anything. SELECT Title, Author FROM Inventory WHERE Price < 25.00 ORDER BY Author; is a perfectly ordinary statement, combining everything built across this module so far: naming columns, filtering rows, and now sorting what's left.

SELECT * FROM Inventory

BookID  Title                   Author       Price
1       The Last Lighthouse     Bennett      24.99
2       Coastal Kitchens        Alvarez      18.50
3       Deep Winter             Castellano   21.00
4       Algorithms Simplified   Chen         29.95
5       Building With Stone     Whitfield    32.00
6       Harbor Lights           Bennett      16.50

SELECT * FROM Inventory
ORDER BY Author

BookID  Title                   Author       Price
2       Coastal Kitchens        Alvarez      18.50
1       The Last Lighthouse     Bennett      24.99
6       Harbor Lights           Bennett      16.50
3       Deep Winter             Castellano   21.00
4       Algorithms Simplified   Chen         29.95
5       Building With Stone     Whitfield    32.00

That last point, the unspecified order between the two Bennett rows, is exactly what a second sort column fixes. The ORDER BY clause can sort by more than one column at once; simply separate the column names with a comma. The statement below sorts first by Author, then uses Title to break any tie within the same author.

Order matters here: the first column listed is the primary sort, and every column after it only comes into play when the columns before it are equal. Writing ORDER BY Title, Author instead would sort primarily by title, with author only breaking a tie between two books that happened to share the exact same title, a far less useful sort for this particular table. There's no limit to how many columns can be chained this way, each one only ever resolving ties left over from the column before it.

SELECT * FROM Inventory
ORDER BY Author, Title

SELECT * FROM Inventory
ORDER BY Author, Title

BookID  Title                   Author       Price
2       Coastal Kitchens        Alvarez      18.50
6       Harbor Lights           Bennett      16.50
1       The Last Lighthouse     Bennett      24.99
3       Deep Winter             Castellano   21.00
4       Algorithms Simplified   Chen         29.95
5       Building With Stone     Whitfield    32.00

SELECT * FROM Inventory
ORDER BY Author DESC

BookID  Title                   Author       Price
5       Building With Stone     Whitfield    32.00
4       Algorithms Simplified   Chen         29.95
3       Deep Winter             Castellano   21.00
1       The Last Lighthouse     Bennett      24.99
6       Harbor Lights           Bennett      16.50
2       Coastal Kitchens        Alvarez      18.50

SELECT * FROM Inventory
ORDER BY Author ASC, Title DESC

BookID  Title                   Author       Price
2       Coastal Kitchens        Alvarez      18.50
1       The Last Lighthouse     Bennett      24.99
6       Harbor Lights           Bennett      16.50
3       Deep Winter             Castellano   21.00
4       Algorithms Simplified   Chen         29.95
5       Building With Stone     Whitfield    32.00

Compare this to figure 6: the two Bennett rows have swapped places. Author is still ascending, but Title is now descending, so "The Last Lighthouse" (further along the alphabet) comes before "Harbor Lights" within the Bennett group.

Module 2's conclusion page compared a plain SELECT across raw SQL, ADO.NET, and Entity Framework Core. Sorting fits that same comparison.

-- Raw SQL
SELECT Title, Author, Price FROM Inventory ORDER BY Author, Title;

// ADO.NET: the ORDER BY clause is part of the SQL text itself
using var cmd = new SqlCommand(
    "SELECT Title, Author, Price FROM Inventory ORDER BY Author, Title;", connection);
using var reader = cmd.ExecuteReader();

// Entity Framework Core
var books = context.Inventory
    .OrderBy(b => b.Author)
    .ThenBy(b => b.Title)
    .ToList();

EF Core's OrderBy and ThenBy map directly onto the primary and secondary columns in a SQL ORDER BY list; reversing either one to descending order uses OrderByDescending or ThenByDescending instead. Underneath, EF Core generates the same ORDER BY Author, Title clause written by hand above, which is exactly why writing it directly first, the way this lesson does, makes the LINQ version easier to read rather than a separate thing to memorize.

Figure 9's mixed-direction sort, Author ascending and Title descending, translates the same way: context.Inventory.OrderBy(b => b.Author).ThenByDescending(b => b.Title).ToList();. Each method in the chain corresponds to exactly one column in the SQL version's ORDER BY list, in the same order, which makes a long LINQ sort chain readable once the SQL it's standing in for is already familiar.

Sorting a number or a date is unambiguous: one value is simply larger or smaller, earlier or later, than another. Sorting text takes a rule, because "larger" and "smaller" aren't inherently meaningful for words. SQL Server decides this using a collation, a set of rules for comparing character data that governs case sensitivity, accent sensitivity, and alphabetical ordering for a particular language or script.

By default, most SQL Server installations use a case-insensitive collation, which is why sorting Author alphabetically doesn't separate "bennett" from "Bennett" into two different groups; they're treated as equivalent for comparison purposes, even though they're stored exactly as entered. A specific collation can be requested for a single ORDER BY using the COLLATE clause shown in the formal syntax below, ORDER BY Author COLLATE Latin1_General_CS_AS would force a case-sensitive comparison instead, sorting capital letters separately from lowercase ones. This isn't something VirtualBookShelf's own queries need to worry about yet, every author name in Inventory is stored consistently, but it's worth knowing the rule exists the first time a sort produces an order that doesn't look quite alphabetical by a stricter definition.

Sorts data returned by a query in SQL Server. This clause is used to:

1. Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set is not guaranteed unless an ORDER BY clause is specified.
2. Determine the order in which ranking function values, a more advanced topic this course hasn't covered yet, are applied to the result set.

ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,...n ]
[ <offset_fetch> ]

<offset_fetch> ::=
{
  OFFSET { integer_constant |
    offset_row_count_expression } { ROW | ROWS }
  [
   FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
  ]
}

Syntax Breakdown

1. ORDER BY order_by_expression
   Specifies the column or expression by which to sort the query results.
2. [ COLLATE collation_name ]
   Specifies a collation for the sorting, if the column is of a character data type.
3. [ ASC | DESC ]
   Specifies the sort direction:
   • ASC for ascending (the default).
   • DESC for descending.
4. [ , ... n ]
   Allows sorting by multiple columns or expressions, separated by commas, exactly as this lesson's figures 5, 6, and 9 demonstrated.
5. <offset_fetch> Clause
   Adds pagination on top of a sort:
   • OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
     Specifies the number of sorted rows to skip before starting to return rows. OFFSET is mandatory whenever this clause is used at all; there's no way to use FETCH without it. ROW and ROWS are interchangeable synonyms, provided for ANSI compatibility.
   • FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
     Specifies the number of rows to return after the offset, and is optional. FIRST and NEXT are also interchangeable synonyms. ONLY is mandatory whenever FETCH is used, to confirm only the specified number of rows comes back.

Example Query

SELECT Title, Author, Price
FROM Inventory
ORDER BY Price DESC, Title ASC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;

This orders VirtualBookShelf's catalog by Price, most expensive first, with Title breaking any price ties, skips zero rows, and returns only the first three. Against this lesson's six-book sample, that's Building With Stone, Algorithms Simplified, and The Last Lighthouse, in that order, the three highest-priced books in the catalog.

Changing OFFSET 0 ROWS to OFFSET 3 ROWS, with everything else unchanged, would skip those same three highest-priced books and return the next three instead, Deep Winter, Coastal Kitchens, and Harbor Lights. This is exactly how a results page displays "page 2" of a sorted list: the ORDER BY stays identical between requests, and only the OFFSET value changes to move through the sorted set a page at a time.

Key Notes

• OFFSET and FETCH are supported by the SQL:2008 standard and later, and by most modern relational database systems, including SQL Server, PostgreSQL, and Oracle Database.
• OFFSET must always be written explicitly whenever this clause is used; it never defaults to anything on its own, and omitting it means the offset/fetch clause isn't being used at all.
• COLLATE is optional and applies only to sorting a character column, affecting things like case sensitivity and accent handling.

If two rows have the same value in the ORDER BY column, what decides which comes first? Nothing reliable, unless a second sort column is added. Figures 4 and 8 both left the two Bennett rows in an unspecified order for exactly this reason; figures 6 and 9 fixed it by adding Title as a secondary sort key. Never assume a tie will come out the same way twice without a column, or combination of columns, that's actually unique.

Does OFFSET/FETCH work without an ORDER BY clause? No. Pagination only makes sense against a defined order; skipping "the first ten rows" is meaningless if the rows aren't in a defined sequence to begin with. OFFSET/FETCH is written as part of an ORDER BY clause for exactly that reason.

Is there an ADO.NET or EF Core equivalent to OFFSET/FETCH? Yes. EF Core's Skip() and Take() map directly onto OFFSET and FETCH: context.Inventory.OrderBy(b => b.Price).Skip(0).Take(3) generates a statement with the same shape as this lesson's own OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY example. ADO.NET has no separate keyword for this; a SqlCommand simply includes the OFFSET/FETCH clause as ordinary text inside the SQL statement itself.

Does ORDER BY run before or after WHERE? After. SQL Server locates the table in FROM, filters rows with WHERE, and only sorts whatever rows survived the filter. A statement combining both, SELECT Title, Price FROM Inventory WHERE Price < 25.00 ORDER BY Price;, filters first and sorts the filtered result, never the other way around.

Is sorting free, performance-wise? Not entirely. Sorting a small table like VirtualBookShelf's Inventory is effectively instant, but sorting a very large table can be costly if SQL Server has to examine and order every row with nothing to narrow it down first. An index built on the sorted column can let SQL Server read rows already in that order rather than sorting them on the fly, a detail worth knowing even though it isn't something this lesson's examples are large enough to demonstrate.

Can ORDER BY sort by a column that isn't in the SELECT list? Yes, for an ordinary query like the ones in this lesson. SELECT Title FROM Inventory ORDER BY Price; is valid: it sorts by Price internally but only displays Title in the result. This is a little like how WHERE can filter on a column that isn't displayed either; sorting and filtering both work against the full row, not just whatever columns happen to be listed in SELECT.

Does ASC or DESC apply to the whole statement, or to one column at a time? One column at a time. ORDER BY Author ASC, Title DESC, the statement behind figures 9 and its result, applies ASC only to Author and DESC only to Title; there's no way to set a single sort direction that applies to every column in the list at once. Each column in an ORDER BY list carries its own direction, defaulting to ascending whenever one isn't stated.

Can a calculated value be used in ORDER BY, rather than a plain column? Yes. ORDER BY Price * QuantityOnHand DESC would sort VirtualBookShelf's books by total inventory value rather than by price alone, highest total value first. SQL Server evaluates the expression for every row and sorts by the result, the same way it would sort by any single column, it just isn't limited to columns that already exist in the table exactly as written.

This lesson covered sorting a result set with ORDER BY: ascending by default, descending with DESC, multiple columns separated by commas to break ties, and OFFSET/FETCH for returning a specific page of sorted rows.

In the next lesson, you will modify rows using the UPDATE statement, the first of this module's statements that changes data rather than only reading it. ORDER BY itself has no place in an UPDATE or DELETE statement, there's no "result set" to sort when a statement is changing rows rather than returning them, but the WHERE clause that decides which rows an UPDATE affects works exactly the way it did back in Lesson 3.

Click the Exercise link below to practice using the ORDER BY clause.
Order By Clause - Exercise