Sorting Data (ORDER BY): Ascending and Descending Sort (ASC, DESC)

When you work with databases, you often want to see some order in your data. For example:

• "Show me the top 10 most expensive products."
• "Which orders were the most recent?"
• "What are the ages of students, starting from the youngest?"

Wanting to organize your data is totally natural: even if you’re a super cool programmer, digging through a chaotic mess of rows isn’t much fun. That’s why SQL gives us a tool to sort rows in the result set — the ORDER BY operator.

Sorting Basics with ORDER BY

ORDER BY is like a magic wand that lets you order rows in your result set. Without it, data comes back in a "random" order (that’s not a database bug, it’s just that the order isn’t guaranteed).

Here’s what the ORDER BY syntax looks like:

SELECT column1, column2
FROM table
ORDER BY column1 DESC;

• column1: the name of the column you want to sort by.
• ASC: ascending sort (this is the default value, so you can skip it if you want).
• DESC: descending sort.

Example

Let’s say we have a students table:

Let’s sort the students by age (ascending):

SELECT name, age
FROM students
ORDER BY age ASC;

Result:

If you change the sort direction to descending:

SELECT name, age
FROM students
ORDER BY age DESC;

The result will be:

Sorting by Multiple Columns

Sometimes one column isn’t enough for sorting. Imagine there are several students with the same age, and you want to add extra ordering, like by name.

Sorting by multiple columns:

SELECT column1, column2
FROM table
ORDER BY column1 DESC, column2 ASC;

Here’s an example for the following table:

Query:

SELECT name, age
FROM students
ORDER BY age ASC, name ASC;

Result:

Here, sorting goes by age first (ascending), and inside each age group — by name in alphabetical order.

Sorting with Aliases

If you use aliases for columns, you can also sort by those aliases.

SELECT name AS "Name", age AS "Age"
FROM students
ORDER BY "Age" DESC;

Result:

Why does this matter? Because it didn’t always work before, and sorting by calculated columns used to be tricky.

Sorting with Calculated Columns

SQL lets you do calculations right in your query, and then sort by the results of those calculations.

Example 1. Simple calculation

SELECT name, age, age * 2 AS doubled_age
FROM students
ORDER BY doubled_age DESC;

Result:

Example 2. Using functions. Let’s say we store product prices in a products table:

Now let’s sort by the rounded price multiplied by 0.9 (for example, with a discount). The ROUND function returns a number rounded to the needed decimal place.

SELECT product_name, price, ROUND(price * 0.9, 1) AS discounted_price
FROM products
ORDER BY discounted_price ASC;

Here’s what the result will look like:

Practical Uses for Sorting

Sorting is super important in these scenarios:

• Building reports, like the top 10 best sales for the month.
• Showing data in user interfaces, like an order list sorted by date.
• Generating lists for analytics, like highlighting groups by priority.

Common Mistakes When Using ORDER BY

Mistakes can pop up at every step. Here are a few things to watch out for:

• If you try to sort by a column that doesn’t exist, you’ll get an error: column "not_a_column" does not exist.
• Sorting by an alias used in a calculated column can be confusing if the alias is set at a higher level.
• If you accidentally forget to specify the sort direction, PostgreSQL will use ASC by default. That’s not an error, but it might give you unexpected results.

Now you know how to control sorting in PostgreSQL using ORDER BY. Remember, well-organized data is half the battle when you’re writing efficient SQL queries. On to the next topic! 😉

2

Task

SQL SELF, level 2, lesson 3

Locked

Sorting in Ascending Order

Sorting in Ascending Order

Open

2

Task

SQL SELF, level 2, lesson 3

Locked

Sorting in Descending Order

Sorting in Descending Order

Open

1

Survey/quiz

Intro to SQL, level 2, lesson 3

Unavailable

Intro to SQL

Intro to SQL

Open