VMware ESXi and vSphere Cluster Management
SQL ORDER BY Clause: Sort Query Results
Learn how to use SQL ORDER BY to sort query results by one or more columns in ascending or descending order.
The SQL ORDER BY clause controls the order in which rows appear in a query's result set. A result set is the collection of rows returned by a SQL query.
ORDER BY sorts the rows returned by a query; it does not change the stored order of rows in the table. Without ORDER BY, the database is free to return rows in any order. An output order that looks consistent during testing is not a guarantee.
Basic ORDER BY Syntax
In a basic query, place ORDER BY after the SELECT list and FROM clause:
SELECT column_name
FROM table_name
ORDER BY column_name;The column after ORDER BY is the sort key: a column or expression used to determine row order. The clause belongs at the end of a basic SELECT statement.
You can select every column and sort by one column:
SELECT *
FROM employees
ORDER BY firstName;You can also sort by a column that is available from the table but not included in the SELECT list, depending on the SQL system and query context. For beginner-friendly queries, selecting the sort column makes the output easier to interpret.
Ascending Order: ASC and the Default Direction
Ascending order means increasing order for numeric values and alphabetical order for text values. SQL uses ascending order by default when no direction is written.
These two queries express the same direction:
SELECT *
FROM employees
ORDER BY firstName;
SELECT *
FROM employees
ORDER BY firstName ASC;ASC is short for ascending. For text, values generally appear from A to Z; for numbers, smaller values appear before larger values.
Descending Order with DESC
DESC is short for descending. It reverses the usual ascending direction: text is generally ordered from Z to A, and numbers are ordered from larger to smaller.
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY firstName DESC;This query displays employees from reverse alphabetical first-name order.
Employee-Table Examples
Assume an employees table with these columns: employeeNumber, lastName, firstName, and extension.
Sort by First Name
This query sorts every employee by firstName in ascending order:
SELECT *
FROM employees
ORDER BY firstName;The displayed first-name sequence is Diane, Jeff, Julie, Mary, and William. The database has not rearranged rows in the employees table; only this result set is ordered.
Make the Direction Explicit
This version selects particular columns and states the ascending direction directly:
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY firstName ASC;The order of columns in SELECT controls the order of columns displayed from left to right. It does not control the order of rows. The ORDER BY clause controls row order.
Sort by Last Name and First Name
To group the display by surname and alphabetize given names within each surname, use multiple sort keys:
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, firstName ASC;Multiple sort expressions are separated by commas. The first expression is the primary sort key, which determines the main ordering. The next expression is a secondary sort key, used only when rows have equal values for the primary key.
The output groups Firrelli employees together, then Murphy employees, then Patterson employees. Within the Patterson group, Mary appears before William.
Sorting by Multiple Columns
Each sort key can have its own direction:
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, firstName DESC;This sorts last names from A to Z. When two employees have the same lastName, their first names are sorted from Z to A.
Ties and Additional Tie-Breakers
A tie occurs when two or more rows have the same value for a sort key. If only firstName is used, employees sharing a first name are tied. Their relative order should not be treated as guaranteed.
Add more sort keys when you need a more predictable presentation:
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, firstName ASC, employeeNumber ASC;Here, employeeNumber is a tertiary sort key. It is consulted only when both lastName and firstName are equal.
General Syntax
SELECT column_name1, column_name2
FROM table_name
ORDER BY column_name1 ASC | DESC, column_name2 ASC | DESC;In this pattern, write one or more columns or expressions after ORDER BY. Separate them with commas, and optionally add ASC or DESC after each item.
How to Interpret Ordered Output
- Read the first ORDER BY item. It establishes the primary row sequence.
- Look for repeated values. Rows with the same primary value form a tie group.
- Apply the next item inside each tie group. A secondary key does not globally reorder all rows; it only orders rows tied on earlier keys.
- Remember the SELECT list. The SELECT list determines which columns and column positions are displayed, while ORDER BY determines the sequence of rows.
Troubleshooting ORDER BY
The Output Is Not Ordered as Expected
If the query has no ORDER BY clause, the database does not promise a particular row order. Add the intended sort column or columns instead of relying on apparent insertion or storage order.
Tied Rows Appear in an Unexpected Sequence
If you sort only by firstName, rows with the same firstName have no defined secondary ordering. Add keys such as lastName and employeeNumber:
ORDER BY firstName ASC, lastName ASC, employeeNumber ASCThe Query Sorts in the Opposite Direction
Use ASC for ascending order and DESC for descending order. Omitting the direction means ascending, not descending.
The Secondary Sort Seems Not to Affect Every Row
In ORDER BY lastName, firstName, firstName is consulted only for employees with equal lastName values. It is not a global first-name sort.
There Is a Syntax Error Near ORDER BY
Place ORDER BY after FROM and separate multiple sort expressions with commas:
SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, firstName ASC;Exam-Relevant Notes
- ORDER BY sorts the result set, not the table's stored rows.
- Without ORDER BY, row order is not guaranteed.
- ASC means ascending and is the default direction.
- DESC means descending.
- The first ORDER BY expression is the primary sort key.
- Later expressions break ties from earlier expressions.
- SELECT column order and row order are separate concepts.
- Multiple sort expressions must be separated by commas.
Related SQL Skills
ORDER BY is commonly used after filtering rows with a SQL WHERE clause lesson when that topic is available. It can also be combined with row-limiting features such as LIMIT or FETCH to return a sorted subset of rows.