Sorting Query Results with ORDER BY

In the relational model, a table represents a set of rows, and sets have no intrinsic order. That means the rows returned by a SELECT statement should be treated as unordered unless you explicitly request a sort. If you need results in a predictable sequence (for reporting, exports, UI display, or “top N” queries), you use the ORDER BY clause.

ORDER BY is evaluated after rows are selected, filtered, grouped, and projected, so it appears at the end of a standard SELECT statement.

Basic ORDER BY Syntax

You can sort by one column or multiple columns, and you can control sort direction using ASC or DESC.

SELECT columns
FROM table
ORDER BY sort_column [ASC | DESC];

• columns — the columns you return in the result set.
• sort_column — the column used to order rows.
• ASC — ascending order (lowest to highest); this is the default.
• DESC — descending order (highest to lowest).

Most SQL dialects allow sorting by a column that is not listed in the SELECT list, but it is a best practice to keep your intent clear and consistent—especially when building queries that will be maintained by others.

Ascending Sort Is the Default

If you omit the sort direction, SQL assumes ASC. In practice, many developers omit ASC and specify DESC explicitly when needed.

SELECT au_fname, au_lname, city, state
FROM authors
ORDER BY au_lname ASC;

| au_fname  | au_lname    | city          | state |
| --------- | ----------- | ------------- | ----- |
| Sarah     | Buchman     | Bronx         | NY    |
| Wendy     | Heydemark   | Boulder       | CO    |
| Hallie    | Hull        | San Francisco | CA    |
| Klee      | Hull        | San Francisco | CA    |
| Christian | Kells       | New York      | NY    |
|           | Kellsey     | Palo Alto     | CA    |
| Paddy     | O'Furniture | Sarasota      | FL    |

ORDER BY with a Simple Customer Table

To see how sorting changes what you see, consider the following Customer table. When you query it without ORDER BY, you might observe a “natural” order, but you should not rely on it. The database engine is free to return rows in any sequence unless you request a sort.

Query 1: No ORDER BY

SELECT *
FROM Customer;

The result set contains the correct rows, but the ordering is not guaranteed.

Query 2: Sort by Lastname

If you want the list alphabetized by last name, add ORDER BY Lastname:

SELECT *
FROM Customer
ORDER BY Lastname;

Sorting by Multiple Columns

If you want a stable, predictable order when last names repeat, add a secondary sort key. This is a common pattern in reports and UI grids:

SELECT *
FROM Customer
ORDER BY Lastname, Firstname;

Now rows are ordered by Lastname first, and for matching last names they are ordered by Firstname. You can extend this pattern to three or more columns as needed.

Descending Sorts

Use DESC to reverse the order. For example, to list customers with “Z” last names first:

SELECT *
FROM Customer
ORDER BY Lastname DESC;

Practical Guidance

• Do not assume row order without ORDER BY, even if results “look consistent” during testing.
• Use multiple columns when you need consistent ordering for ties (lastname + firstname is a standard example).
• Be explicit for readability when a query’s sort order is business-critical.

In the next lesson, you will apply ORDER BY to more realistic queries, including sorting numeric values, sorting with NULL values present, and combining sorting with filtering using WHERE.