Skip to main content

Sorting data with ORDER BY

Premium

In this lesson, we'll be exploring how to sort data using the SQL statement "ORDER BY." Sorting is an important part of working with data because it allows you to quickly understand the distribution of values in a particular column, identify outliers or patterns. By sorting your data, you can easily answer questions like: what are the largest or smallest values in a column, or which values are most frequently repeated?

Basic Syntax

In SQL, you can sort data by specifying the column that you want to sort on in the ORDER BY clause. By default, the ORDER BY sorts data in ascending order. For example:

SQL
SELECT * FROM courses ORDER BY course_name;

In this example, the data in the courses table is sorted based on the course_name column, and the result will show the courses ordered alphabetically from A to Z, or from smallest to largest for numerical columns.

Deciding sort order with ASC and DESC

You can also sort data in descending order by using the DESC keyword after the column name in the ORDER BY clause. For example:

SQL
SELECT * FROM courses
ORDER BY course_name DESC;

This will result in the courses being ordered in reverse alphabetical order, Z to A.

When sorting numerical data, ascending order means that the values will be displayed in increasing order. Descending order, on the other hand, displays the values in decreasing order. For example:

SQL
SELECT * FROM students
ORDER BY student_id;

In this example, the student_id column is sorted in ascending order, so the results will show the students in order of increasing student ID.

Sorting with multiple columns

You can also sort based on multiple columns. For example:

SQL
SELECT * FROM students
ORDER BY course_id, student_id;

In this case, the results are first sorted by the course_id column, and then the student_id column. This means that the students in each course will be ordered by their student IDs.

It's important to keep in mind that the order of the columns in the ORDER BY clause determines the sort priority. For example, if you want to sort by course first and then by student ID, the clause would look like this:

SQL
ORDER BY course_id, student_id

And if you want to sort by student ID first and then by course, it would look like this:

SQL
ORDER BY student_id, course_id

Try it yourself

Let's try an example ourselves. Given a customers table, write a query that returns all customers ordered alphabetically by first_name first and last_name second, i.e. "Bruce Banner" comes before "Bruce Wayne".

In the next lesson, we'll learn about the LIMIT clause and how it can be used in conjunction with the ORDER BY clause to return a specified number of rows in a sorted order. This can be particularly useful for large datasets where you only need to see a certain portion of the data at a time.

Next Lesson