Sorting by Multiple Columns: Sort Order and Priorities

Imagine you’re working with a huge database of students. Sorting them just by last names is already useful, but what if your table has students with the same last name? In that case, to make the output more readable, you can add sorting by first name. Or another example: you want to order a list of orders first by date, then by the total order amount. And this is where sorting by multiple columns comes to the rescue.

Sorting by multiple columns lets you: 1. Arrange data in a clearly defined order; 2. Control sorting priorities (first by one column, then by another); 3. Output data in a more structured and readable way.

Syntax for Sorting by Multiple Columns

To sort data by several columns, you use the familiar ORDER BY operator, but now you list several columns separated by commas. PostgreSQL sorts first by the first column, then (if there are equal values) by the second, and so on.

Here’s the general syntax:

SELECT columns
FROM table
ORDER BY column1 direction1,  column2 direction2, ... ;

Where:

• column1, column2, ... — these are the columns you want to sort by.
• direction1, direction2, ... — this is the sort direction (ASC — ascending, DESC — descending).
• This parameter is optional, by default ASC is used.

Example 1: Sorting Students by Last Name, then by First Name

Let’s imagine a students table with student data:

Our task is to sort students first by last name (last_name), then by first name (first_name).

SELECT id, first_name, last_name, age
FROM students
ORDER BY last_name ASC, first_name ASC;

Result:

As you can see, the rows are ordered first by last name (Kim, then Lin), and then by first name (Anna, Maria, etc.). This is super useful when you need to quickly find someone in a list or sort a gradebook.

Example 2: Sorting Orders by Date and Amount

Let’s imagine an orders table with order data:

We want to sort orders first by order_date, then by total in descending order.

SQL query:

SELECT order_id, customer_id, order_date, total
FROM orders
ORDER BY order_date ASC, total DESC;

Result:

As you can see, orders with the same date are also sorted by amount.

Working with NULL When Sorting: NULLS FIRST and NULLS LAST

In SQL, there’s a special value — NULL, which means “no value.” We’ll talk more about how to work with it in a couple of levels. But now is a good time to show how you can control it when sorting.

When you sort columns containing NULL, PostgreSQL has to decide where to put the empty values: at the beginning or at the end.

By default:

• With ORDER BY ... ASC: NULL goes at the end (NULLS LAST)
• With ORDER BY ... DESC: NULL goes at the beginning (NULLS FIRST)

Sometimes this behavior is inconvenient — and you can control it explicitly using NULLS FIRST or NULLS LAST.

Syntax:

ORDER BY column ASC NULLS FIRST
ORDER BY column DESC NULLS LAST

Example: Sorting by Grades, Where NULL Means "Grade Not Yet Assigned"

SELECT student_id, grade
FROM grades
ORDER BY grade DESC NULLS LAST;

This way, we’ll get the best students first, then the weaker ones, and at the very end — those who don’t have a grade yet (NULL).

Example: The Other Way — Show Students Without a Grade First

Let’s say we have a grades table where some students haven’t received a grade yet (NULL):

We want to show students without a grade first, then the rest in ascending order of grades.

SELECT student_id, grade
FROM grades
ORDER BY grade ASC NULLS FIRST;

Result:

The NULLS FIRST operator together with ORDER BY grade ASC makes sure that rows with NULL come first, and then the grades go up in order.

About Gotchas and Common Mistakes

Newbies often forget to specify the sort direction for each column. If you don’t specify the direction, PostgreSQL uses ASC by default. It’s also common to forget that the order of columns in ORDER BY matters: first, data is sorted by the first column, and only then the second column is used if the first column’s values are equal.