Welcome back to our SQL learning journey! It’s been a week and a half since my last post on using LIMIT. Work got crazy and I got the flu! 🙁 But we’re back in action. Today, we’ll delving deeper into SQL statements in PostgreSQL: ORDER BY.

Let’s jump in. Why would you need a statement that orders data? Whenever you query data from a table, PostgreSQL will by default return the rows in the order they were inserted into the table. (Read: not the order you want.)

To sort the result set from your SQL query, you can use the ORDER BY clause in the SELECT statement and specify a certain ascending or descending order.

Sample ORDER BY Clause Syntax

Here’s an example of the ORDER BY clause would look like within a SELECT statement.

SELECT column_1, column_2

FROM table_name

ORDER BY column_1 ASC / DESC;

Some important notes for the ORDER BY clause:

• Specify the column you want to sort by with the ORDER BY clause
  • If you sort the results by multiple columns, use a comma to separate between the two columns
• Use ASC to sort the results in ascending order
• Use DESC to sort the results in descending order
• Should you leave the ORDER BY clause blank, it will use ASC (ascending) by default

ORDER BY Clause Examples in SQL Statements

Alright, first up. We’ll do a basic SELECT statement, adding both ORDER BY and LIMIT clauses. Code and screen shot below. The query should select the first and last name columns from the customer table, ordered by last name ascending (A > Z), returning only the first 10 rows.

SELECT first_name,last_name  FROM customer

ORDER BY last_name ASC

LIMIT 10;

Let’s try another example. What if we want to do an advanced / multiple sort? Let’s try it out, and change it up ever so slightly from the first sample query.

SELECT first_name,last_name

FROM customer

ORDER BY first_name ASC,

last_name DESC;

If we scroll down to the first set of duplicate first names, we’ll see the last name has been presented in descending order (Z > A).

More Details About the ORDER BY Clause

Did you know? In PostgreSQL, you can ORDER BY columns that aren’t explicitly selected within the SELECT statement? E.g. We could only select first and last name, but order by their address_id value. Interesting, yes? Important caveat, other SQL programs, such as MySQL, may not let you do this.

Below:

SELECT first_name FROM customer

ORDER BY last_name ASC;

Wrap-Up

Thanks for joining me and hopefully you’ve learned the essentials for the ORDER BY clause. Feel free to check out the entire journey on how to learn SQL, thanks!

Zach