Lesson 7 of 26 · see the practice database

Sorting with ORDER BY

A database does not promise to return rows in any particular order. They may come back in the order they were stored today and in another order tomorrow. If the order matters to you, say so with ORDER BY.

Sorting by one column

ORDER BY goes at the end of the query, after WHERE if there is one.

SELECT name, price
FROM products
ORDER BY price;

The default is ascending order: smallest number first, A before Z, earliest date first. You may write ASC to say so, but nobody does.

Largest first

Add DESC, short for descending.

SELECT name, price
FROM products
ORDER BY price DESC;

Sorting text and dates

SELECT name, city
FROM customers
ORDER BY name;

SELECT id, order_date, status
FROM orders
ORDER BY order_date DESC;

The second query shows the newest orders first, which is how most order lists on websites are arranged.

Sorting by more than one column

List several columns. The result is sorted by the first. Rows that tie on the first are sorted by the second, and so on.

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

The products are grouped by category in alphabetical order. Inside each category, the dearest comes first. Notice that DESC applies only to the column it follows. Each column in the list has its own direction.

SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC, name;

The third column settles ties between people in the same department on the same salary.

Filtering, then sorting

WHERE comes before ORDER BY. The database first picks the rows, then sorts them.

SELECT name, price
FROM products
WHERE category = 'Home'
ORDER BY price;

Sorting by a calculation or an alias

You can sort by something worked out. And unlike WHERE, ORDER BY can use an alias, because sorting happens after the columns have been named.

SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

Sorting by a column you do not show

SELECT name
FROM employees
ORDER BY hired;

The names come back in order of joining date, although the date is not shown.

Sorting by position

You may give the number of a column in the SELECT list instead of its name. ORDER BY 2 means "sort by the second column".

SELECT name, salary
FROM employees
ORDER BY 2 DESC;

It is quick to type when exploring. Avoid it in queries you keep, because adding a column to the list later silently changes what the number points to.

Where do NULLs go?

Rows with no value in the sort column are kept together at one end.

SELECT name, email
FROM customers
ORDER BY email;

SQLite, MySQL and SQL Server put NULL first in ascending order. PostgreSQL and Oracle put it last. PostgreSQL, Oracle and SQLite let you choose with NULLS FIRST or NULLS LAST.

SELECT name, email
FROM customers
ORDER BY email NULLS LAST;

How text is sorted

Text is compared character by character. Two things can surprise you.

• Case. In SQLite and PostgreSQL, capital letters sort before small letters by default, so "Zebra" comes before "apple". MySQL and SQL Server usually ignore case. In our data every name starts with a capital, so you will not notice.
• Numbers stored as text. If a column holds text such as '9', '10' and '100', it sorts as text: '10', '100', '9'. Numbers should be stored in number columns.

Ties have no fixed order

If two rows have the same value in every sort column, the database may return them in either order. When you need the same order every time, for example when showing results a page at a time, add a column that is unique, such as id, at the end of the ORDER BY.

SELECT name, salary
FROM employees
ORDER BY salary DESC, id;

The order of the parts so far

SELECT, FROM, WHERE, ORDER BY. Sorting is nearly always last.

Try it yourself

SELECT name, salary FROM employees ORDER BY salary DESC;

SELECT name, city FROM customers ORDER BY city, name;

SELECT name, stock FROM products WHERE category = 'Grocery' ORDER BY stock;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.